Excel Mode Fomula Help needed

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,454
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
 
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.
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,454
mine doesn't in 2003, comes up with value error
 

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?
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,454
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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top