cannot exclude a cell when using AVERAGEIF

surfing69

New Member
Joined
Jan 27, 2021
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,036
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 27, 2021
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
fantastic - this worked. Thanks.

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,036
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 27, 2021
Messages
7
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,036
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 27, 2021
Messages
7
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,036
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 27, 2021
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,036
Office Version
  1. 365
Platform
  1. Windows
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).
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,130,380
Messages
5,641,796
Members
417,239
Latest member
AymericA

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top