Please help revert 07 formula to 02

[wraith972]

I have been wrestling with this for couple of hours now and was hoping to get pointed in the correct direction.

I have this formula in Excel 07 and of course works beautifully.

=IFERROR(AVERAGEIF(March!$D$3:$D$10000,"Yes",March!$P$3:$P$10000),0)

I've tried this but it's not factoring in the Yes....

=IF(ISERROR(AVERAGE(IF(March!$D$3:$D$10000="Yes",AVERAGE(March!$P$3:$P$10000)))),0,AVERAGE(IF(March!$D$3:$D$10000="Yes",AVERAGE(March!$P$3:$P$10000))))

Obviously, I can't use the 07 formula because no one else in the department has it. Any help is always greatly appreciated.

 

[schielrn]

I assume you are confirming this with control+shift+enter and not just enter? I'm not positive if you need the extra average either maybe try:

=IF(ISERROR(AVERAGE(IF(March!$D$3:$D$10000="Yes",March!$P$3:$P$10000))),0,AVERAGE(IF(March!$D$3:$D$10000="Yes",March!$P$3:$P$10000)))

Hope that helps.

 

[wraith972]

schielrn, that did it! I was confirming the formula but was just over thinking it like I sometimes do. I'm getting so use to the new formulas; I really am forgetting some of the older ways.

Thanks!!

 

[Aladin Akyurek]

=IFERROR(AVERAGEIF(March!$D$3:$D$10000,"Yes",March!$P$3:$P$10000),0)

Cheaper to run...

Control+shift+enter, not just enter:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
    AVERAGE(IF(March!$D$3:$D$10000,"Yes",March!$P$3:$P$10000))))

 

[wraith972]

Wow, Aladin... After the many green beers I consume this evening, that may make more sense.

Obviously the first part of the forumla is doing the same as the iserror but will you please explain how?

Thanks as always!!

 

[Aladin Akyurek]

Wow, Aladin... After the many green beers I consume this evening, that may make more sense.

Obviously the first part of the forumla is doing the same as the iserror but will you please explain how?

Thanks as always!!

CHOOSE calculates 2 results: 0 and avg which is a number or #DIV/0! and puts them in an array, like in {0,2.4} or {0,#DIV/0!}. LOOKUP with that big number as the lookup value returns the last numeric value from the array CHOOSE constructs.

How LOOKUP does its job is explained graphically in the following link:

http://www.mrexcel.com/forum/showthread.php?t=310278