Excel formula help needed

Question

Good evening,

I am having an issue with the following fomula:

=averageif(E22,E27,E32,E37,E42,E47,E52,E57,E62,E67,E72,E77,E82),(">0")

I am trying to average selected cells down a column that are greater than zero(o), however, each time I attempt to complete the formula the system states that I entered to many arguments for the function. I am hoping that someone on this platform may be able to assist me in solving this issue.

Thank you

Answer 1

Hi,

This should work

=SUMPRODUCT((MOD(ROW(E22:E82)-1,5)=1)*E22:E82)/SUMPRODUCT((MOD(ROW(E22:E82)-1,5)=1)*(E22:E82>0))

Hope this helps.

Answer 2

AFAIK the AVERAGEIF function does not accept non-contiguous ranges.

You could try AutoFilter and set the Filter to the required numbers and then use SUBTOTAL to find the average of the displayed numbers.

More on SUBTOTAL function at the following Microsoft link.

https://support.microsoft.com/en-us/office/subtotal-function-7b027003-f060-4ade-9040-e478765b9939

Answer 3

Hi Brandon.matlock!

Yes, the AVERAGEIF formula you applied above will always return too many arguments.

It seems you want to select certain cells only, but I do not understand the criteria you use in selecting them.

If the criteria was just because they are greater than zero then use the function =AVERAGEIF(E22:E82,">0"), which will give the average of the numbers from E22 to E82 greater than zero

If not I will suggest you form a new column for those cells and then find their average.

Check for more on how to use the AVERAGEIF function: https://support.microsoft.com/office/faec8e2e-0dec-4308-af69-f5576d8ac642

Kind Regards, Shakiru