Formula problem---Arrgggh

Hi,

I am using the following formula:

=SUMIF(Y12:Y35,"<0.70833",M12:M35)/(IF(AND(M12:M35>0,Y12:Y35<0.70833),COUNTIF(Y12:Y35,"<0.70833")))

When trying to calculate an average in Excel 2003. It works on "Value" date - but when I am applying the formula to cells that already have formulas in them - it doesn't seem to cooperate... Any ideas?

Thanks in advance


jazman

Originally Posted by AliGW

Why do you not have quote marks in the AND section of the formula?

Hi AliGW... where would I need to put the quote marks?

AND doesn't work on ranges. You seem to want the average if Y < 0.70833 and if M >0, so you could use this array* formula:

=AVERAGE(IF((Y12:Y35<0.70833)*(M12:M35>0),M12:M35))

*An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual < Enter >

Hope this helps.

Pete

EDIT: Of course, I am assuming that your profile is correct and you are using XL2003. If you are now using a later version, you could use the AVERAGEIFS function.

or this regular formula

Formula:

Please Login or Register  to view this content.