#### wraith972

##### Board Regular
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.

Code:
``=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....

Code:
``=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.

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### schielrn

##### Well-known Member
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:

Code:
``=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

##### Board Regular
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!!

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

Cheaper to run...

Control+shift+enter, not just enter:
Code:
``````=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
AVERAGE(IF(March!\$D\$3:\$D\$10000,"Yes",March!\$P\$3:\$P\$10000))))``````

#### wraith972

##### Board Regular
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!!

##### MrExcel MVP
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:

Replies
4
Views
214
Replies
3
Views
650
Replies
4
Views
444
Replies
5
Views
567
Replies
2
Views
402

1,191,054
Messages
5,984,388
Members
439,883
Latest member
onions44

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back