Adjust Average If Formula

[dunmore83]

Hi,

I need to adjust the following formula so it only returns the average for cells in column AG12:AG377 which have a value (i.e. ISNUMBER)

{=AVERAGE(IF(('Milk-Cows-Day'!$D$12:$D$377>=EDATE($G20,0))*('Milk-Cows-Day'!$D$12:$D$377<=EOMONTH($G20,0)),'Milk-Cows-Day'!$AG$12:$AG$377,""))}

Currently it is return the average to include all blank cell values in AG12:AG377

Any helpers ??

 

[Matty]

Hi,

Empty or blank cells are usually ignored by the AVERAGE function.

When you say "blank cell values", do you mean something else?

Matty

 

[dunmore83]

Thanks for your reply.

No - the cells are blank.

For example in the month of February 2018 the sum of the values in the first 4 days is 164 (the other 24 cells in column AG for Feb are still blank) & the formula is returning 5.86 as the average (i.e. 164/28)

 

[Matty]

Just add a further test to the formula, like:

[COLOR=#333333]=AVERAGE(IF('Milk-Cows-Day'!$D$12:$D$377>=EDATE($G20,0),IF('Milk-Cows-Day'!$D$12:$D$377<=EOMONTH($G20,0),[/COLOR][COLOR=#ff0000]IF(ISNUMBER('Milk-Cows-Day'!$AG$12:$AG$377)[/COLOR][COLOR=#333333],[/COLOR][COLOR=#333333]'Milk-Cows-Day'!$AG$12:$AG$377))))[/COLOR]

Array entered, i.e. with CTRL+SHIFT+ENTER.

Matty

 

[dunmore83]

Great thanks!

 

[Matty]

No problem.