1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Mode Fomula Help needed

Discussion in 'Business Applications' started by doubletake, Jan 5, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. doubletake

    doubletake Thread Starter

    Joined:
    Jan 5, 2011
    Messages:
    2
    Hello everyone, I wonder if one of you can help? I am trying to use the mode formula in Excel but keep getting the same problem. The formula I am using is below

    =MODE(X4:AC4,AH4:AI4,AL4:AM4,AP4:AQ4,AT4,AW4:AY4,BB4:BC4,BF4:BG4,BJ4:BK4)

    Essentially I have various fields of data that I want the mode number from. Sometimes these fields of data are will be spread over a few columns (X4 to AC4, for example) or will be a cell in a single column (AT4, for example).

    The problem I am having with the above formula is with the single cell/column (AT4). The formula works and MODE number is show IF there is a number in AT4, however if the cell is blank, the result is #VALUE!. The problem is that the cell WILL be blank sometimes (until colleagues enter the data). Is there a way around this problem?

    I have tried to list all the cells with commas between them for example

    =MODE(X4,Y4,Z4,AA4,AB4,AC4,AH4,AI4…)

    However that does not work, still get #VALUE!

    I cannot have the formula =MODE(X4:BK4) either as their are other columns between the data fields with other numbers and I do not want them to be counted.

    I hope all that makes sense, sorry if longwinded, but this is my first post. Any help would be appreciated.

    Thanks DS
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    is it only AT4 that maybe blank ?
    and if it is blank what do you want to do ?

    maybe a better solution, but if would use an if and test the cell first
     
  3. Center

    Center

    Joined:
    Jan 13, 2009
    Messages:
    38
    That's odd, because (at least in Excel 2003), MODE appears to work whether there are blanks or not.
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    mine doesn't in 2003, comes up with value error
     
  5. doubletake

    doubletake Thread Starter

    Joined:
    Jan 5, 2011
    Messages:
    2
    Thanks Etaf / Center. No anyone of the cells could be blank, not just AT4, however on the fields that are a range eg: X4:AC4 if one is blank it doesn't matter the formula works, it is only if the single cell AT4 is blank. Essentially the spread sheet is used to have various test results which are entered by various people over a period of time.

    How would I use an 'IF' in the formula to test things? Could I use it with the mode, eg: calculate the mode of all these cells, however ignore if any are blank?
     
  6. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    not sure of the best way to do that - you will need to test each cell and if blank exclude from the calculation
    still thinking on a solution - others may help further
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/972886

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice