MODE function

**dpc99999** · 09-14-2009, 10:10 AM

Hi

I’m using excel 2003 and I need help with the MODE function.

I have a spreadsheet with 142 work types’ rows of work with each one showing the last 13 months of work received. I have set up a mode function for each work type, but for the ones when excel cannot work out the mode it puts n/a in and I have to do them manually, by looking at the last 13 months data and basically guessing a mode figure – which is a pain (e.g. if there was 13 different numbers from say 120 – 135 excel would give n/a but I would put the mode at around 128. As this spreadsheet gets updated monthly is there a way I can trick excel into giving a mode figure, or a figure close to what the mode should be.

I have attached a sample of the spreadsheet.

Thanks for any help

**ChemistB** · 09-14-2009, 10:26 AM

When I opened your workbook, none of the MODE calculations came up "n/a". Which values are giving you this? Is there anything unusual about that row of data?

**daddylonglegs** · 09-14-2009, 10:41 AM

Perhaps calculate the median if the mode is #N/A, i.e.

=IF(ISNA(MODE(C5:O5)),MEDIAN(C5:O5),MODE(C5:O5))

**dpc99999** · 09-14-2009, 12:05 PM

Thanks daddylonglegs for the formula - if there is no may of getting excel to show mode - then that's they way I will have to go.

p.s there was no n/a on that spreadsheet as it was a sample of last months and had already removed the n/a and put in my own n/a's - sorry if this confused anyone.