MrExcel Publishing
Your One Stop for Excel Tips & Solutions

average limit problem, please help~~~


Posted by valshu on April 25, 2001 5:46 PM

Hi, does anyone know how to increase the limit for the content inside an average, min, or max function in excel?

Ex. = average(E10,E13,E21,E23,..... E401)
But the limit for those cells are only 30. Otherwise it thinks it's an invalid formula. Is there any way I can increase it to refer to more cells? Thank you very much.

Best regards
valshu


Posted by Mark W. on April 25, 2001 5:57 PM

valshu, the limits of the AVERAGE() function aren't
cell dependent, but rather *argument* dependent!
There's nothing to prevent you from defining a
name such as Range and assigning the reference...
E10,E13,E21 and so on, the using the array formula
={AVERAGE(Range)}. I'm not suggesting that this is
the best solution to your problem. I'm just
making sure you understand the true limitations
of the AVERAGE() function.

Posted by Mark W. on April 25, 2001 6:08 PM

Aah! I just double-checked something... the AVERAGE()
function will recognize an array argument so there's
no need to enter =AVERAGE(Range) as an array formula.

Now I must ask, "Why are you only picking certain cells
between rows 10 and 401?"

Posted by valshu on April 26, 2001 11:55 AM

Oh well, I guess I will try to take the average fucntion internally in the VBA rather than writing "=average(.....)" into the cells.

Or I may copy the contents from those certain cells and paste onto the very bottom of the spreadsheet and take "=average(xxx : xxx)" then. Thank you very much, Mark. ^^