Aggregate Function Not Working As Expected

[tjnelso]

I have a range of values, for example R11:R25, for which I need to reciprocate and take the average. I need to do this all in one step, using a function, and the range may contain blank cells. If I try to reciprocate the blank cells I get a DIV/0 error so I'm trying to use the aggregate function as follows to work around that:

=AGGREGATE(1, 6, 1/(R11:R25)).

Based on my understanding this should take the array of values R11:R25; reciprocate them and then, ignoring errors, calculate the average. However, the function isn't working as expected and is giving a #VALUE! error. Is anyone able to explain why this doesn't work and how I might achieve my stated goal?

[Jonmo1]

The Aggregate function will only work with Arrays in the options 14 and above.

I think you'll need to do

=AVERAGE(IF(ISNUMBER(R11:R25),IF(R11:R25<>0,1/R11:R25)))

Entered as an array with CTRL + SHIFT + ENTER

[easyexcelanswers]

I just made a video this morning on using aggregate function

=AGGREGATE(1,6,A2:A100) I think you might have an extra 1 in there
https://www.youtube.com/watch?v=DACXUZJfNo8

[Jonmo1]

Wait, is that the only reason you added the 1/ ? As an attempt to 'Cause' an error so that Aggregate would ignore it?
So you're not wanting the average of 1/R11 1/R12 1/R13 etc ?

The plain old Average function will ignore blanks by itself.
=AVERAGE(R11:R25)

[ConneXionLost]

Try an array formula:

Please Login or Register  to view this content.

Confirm as an array formula with Ctrl-Shift-Enter

Cheers,

[tjnelso]

All,

Thanks for the replies. Yes, an array formula is an obvious way to go but needs to be avoided because of the slow speed at which they calculate in large files. I took the reciprocal in the aggregate function because I'm trying to average the reciprocals of the values in the specified range. So there's no way to do this without an array formula?

[JohnTopley]

Calculate reciprocals in "helper" column and average these?

[ChemistB]

Sure, create another column with =IFERROR(1/R11, "") copied down and then take the average of that column. You can always hide that second column or even put it on a different sheet. This would be much faster than a large array. Why are people so anti helper columns?