Summing & Averaging of absolute values vs. empty cells

Frank

New Member
Joined
Oct 31, 2002
Messages
2
I have a series of derived 'text' numbers to some formulas. Based on an answer I had received earlier, I made sure that these results became true numbers, and hence could be averaged, by using the edit/paste special feature and checking 'Add'.

So, now all zero values are counted and included in any averaging. This is fine as long as EVERY respondent to the questionnaire in question provided a response. Not always true. Given that I now have made the cell value = to a zero value, how do I EXCLUDE any cells that are empty (i.e. blank)in my averaging. By way of example, if all 10 respondents answered and there was a zero value, then that would be O.K. for the denominator on the average to be 10. But, if only 4 of 10 respondents answered and the other 6 cells are empty, I would want to average the received responses over only 4 and not 10 responses received. Is there a way I can do this?
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
yes!! post more info about your data ranges etc - details required 'cos a straightforward average() will ignore blank cell...

paddy
This message was edited by PaddyD on 2002-11-04 20:47
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,612
Members
414,080
Latest member
penguin23

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