cannot exclude a cell when using AVERAGEIF

[surfing69]

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
	C	D
4		200
5
6
7	67.8	33.90%
8	173.3	86.65%
9	0	0.00%
10	0	0.00%
11	0	0.00%
12	120.55	60.28%
13	#DIV/0!	37.30%
Sheet1

Cell Formulas
Range	Formula
D7	D7	=C7/D4
D8	D8	=C8/D4
D12	D12	=AVERAGEIF(D7:D11,"<>0")
D13	D13	=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

 

[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:

=TEXT(C8/D4,"0.00%")

 

[surfing69]

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]

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]

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]

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:

=(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).