cannot exclude a cell when using AVERAGEIF

surfing69

New Member
I have the following range where some cells are either blank or contain zero. However there is one cell that contains a number generated by a formula. However despite formatting that cell as 'text' , the AVERAGEIF function still sees it as a number.

For the range below, the cell containing the number 86.65% which is has been generated from another formula i.e. =C8/D4 is still seen by the AVERAGE OR AVERAGEIF formula even if i have formatted this cell as 'text'.

Book2
CD
4200
5
6
767.833.90%
8173.386.65%
900.00%
1000.00%
1100.00%
12120.5560.28%
13#DIV/0!37.30%
Sheet1
Cell Formulas
RangeFormula
D7D7=C7/D4
D8D8=C8/D4
D12D12=AVERAGEIF(D7:D11,"<>0")
D13D13=STDEV.S(IF(D7:D11>0,D7:D11))
Press CTRL+SHIFT+ENTER to enter array formulas.

The average for this range should be 1 if cells D8 to D11 had been excluded.

If formatting cell D8 as Text does not exclude the value in this cell, how can i adjust the AVERAGEIF formula to achieve this? However I still want to see the 'value' in cell D8 but dont want it as part of the calculation.

Thanks

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

Joe4

Welcome to the Board!

Formatting the value as Text is not enough. That just changes the display, not the nature of the data.
If you want to exclude it, use the TEXT function to convert the formula so it returns a Text value like this:
Excel Formula:
``=TEXT(C8/D4,"0.00%")``

surfing69

New Member
fantastic - this worked. Thanks.

However I was still wondering if the AVERAGEIF formula could have been amended to exclude that particular cell?

Joe4

However I was still wondering if the AVERAGEIF formula could have been amended to exclude that particular cell?
Sure, if you are able to come up with criteria that only that cell meets.

surfing69

New Member

if i knew how to do this, i wouldnt be on this site. Would you be able to help with this?
thanks

Joe4

if i knew how to do this, i wouldnt be on this site. Would you be able to help with this?
I think you misunderstood what I was saying.

What is that "criteria"?
What is it that makes that row different than the others?
What is the logic for determing which rows to include and which to exclude?

You haven't really explained that detail to us, so only you know right now.
If you explain the logic behind making that determination, then it might be possible for us to try to exclude it somehow.

surfing69

New Member

i thought i had provided the relevant info. I guess not.....

For the column range D7:D11 I would like to exclude all cells showing 0 and also the contents of the cell D8 and calculating the average. I was curious if this could be done using the AVERAGEIF or AVERAGEIFS formula.

I don't know how else to describe my query.

Joe4

Remember, that while your problem is very familiar to you, it is not to us.
So we may ask for clarification on those sort of things. We don't like to make "assumptions". We don't want to spend a lot of time crafting a solution based on our assumptions, which may be wrong, and then leading to a solution that does not work for you (it has happened to me more than a few times).

From your original question, I was not sure if it will ALWAYS be cell D8 that is excluded, or that was just one example (and it could be other cells).
So, is it ALWAYS going to be excluding exactly cell D8, and not any other cell (or at no time would you like to include D8)?

surfing69

New Member
on this occasion, it is only D8 that I need excluding-
if in the future, another cell in a different range needs excluding i will hopefully be able to apply the solution found here.

Joe4

on this occasion, it is only D8 that I need excluding-
if in the future, another cell in a different range needs excluding i will hopefully be able to apply the solution found here.
OK, if there was some "rhyme or reason" or some way of identifying these cells (i.e. maybe what appears in the column next to them), we might be able to come up with a dynamic solution that you wouldn't have to manually edit (which is why I was asking). We can still come up with a solution if not, but that formula would just have to be manually maintained/edited if things change or you want to use it somewhere else.

So, I have not seen any way to get the AVERAGEIF function to work on a non-contiguous range. Basically, what I have found is that you need to create your own average function.
I think this should do what you want:
Rich (BB code):
``=(SUM(D7:D11)-D8)/((COUNTIF(D7:D11,">0"))-(D8>0))``

Note that this only works if you want to exclude one cell in your range.
If you want to use this elsewhere, you would just need to change the blue parts (the entire range) and the red part (the cell to exclude).

Replies
7
Views
455
Replies
3
Views
88
Replies
5
Views
252
Replies
6
Views
170
Replies
6
Views
123

1,129,538
Messages
5,636,897
Members
416,948
Latest member
Jkpang

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.

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