# Excel Mode Fomula Help needed

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

Not open for further replies.

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. ### etafModerator

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

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. ### etafModerator

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

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. ### etafModerator

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

As Seen On