Array function returns #NUM! result

**TonyLupton** · 01-29-2009, 03:15 AM

I'm using an array function to calculate the 95-percentile of a data. I have a number of data values per hour, and I have a cell in each row that truncates the timestamp of a result down to the hour.

My array function is {=PERCENTILE(IF($A2=DS!$AM:$AM,DS!$T:$T,FALSE),0.95)}, where column A2 is the key (the hour in question), DS!AM contains the truncated timestamp, and DS!T contains the values that I want to determine the 95-percentile of.

I've used this method quite successfully in a number spreadsheets, but I have one spreadsheet that returns some #NUM! error for some hourly values. I can see no reason why some hours work and not others. Comparisons between keys and truncated timestamps are ok, and the number of data values (12) is the same from one hour to the next.

Any clues?

**DonkeyOte** · 01-29-2009, 04:57 AM

Pre 2007 you can't use whole column references like AM:AM or T:T

**TonyLupton** · 01-30-2009, 02:38 AM

Thanks. However this is with 2007.

Tony

**DonkeyOte** · 01-30-2009, 04:07 AM

What does =COUNTIF(DS!AM:AM,A2) return ?
If 0 you will get a #NUM error in your Percentile array.

Note: although entire column references are permitted in 2007 I would still advise against using them, performance will be affected given magnitude of range... it may be you have that many records (I'd hope not) -- better to use as small a range as is feasible.