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
 

surfing69

New Member
Joined
Jan 27, 2021
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
wow - long formula . I did try to fiddle with the AVERAGE Function but could find a way. The help pages on MS excel always seem to have adjoining cells i.e. A1:A6 and B1:B6 etc.. - not sure why every example had this format.

Thanks for your input
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
wow - long formula
That's nothing! Browse around here a bit, and you will find some crazy formulas that are multiple lines long! ;)

Basically, we are doing our own average, which is the total values added up, divided by the number of entries.
So, for the Sum part, we just summed up the entire range, and subtracted the one cell that we wanted to exclude (adding 0s to the sum does not change anything).
Then, for the count part, we just counted all values that are greater than zero, and subtracted one if F8 was greater than 0.
 

surfing69

New Member
Joined
Jan 27, 2021
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
i was tempted to go down this route myself but I thought there must be an easier way which involved the AVERAGE function. Oh well - u learn something new every day !
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
The AVERAGEIF function works just fine for contiguous ranges. It is when you have non-contiguous ranges, like in your example, that give it trouble.

Interestingly, if you name your range, and then use the named ranges in the formulas, AVERAGE will work with non-contiguous ranges, but it appears AVERAGEIF will not.
However, since you want to exclude zeroes, you cannot use AVERAGE.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,994
Messages
5,622,053
Members
415,875
Latest member
Tarali

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