| View previous topic :: View next topic |
| Author |
Message |
rnrasmus
Newbie
Joined: 17 Oct 2006
Posts: 1
|
 Posted: Tue Oct 17, 2006 7:54 am Post subject: disregarding cells with no data entered when calc'g avg.s? |
 |
|
I am developing a scorecard that includes scoring elements that may not always be relevent. Is there a way I can instruct Math to disregard that element when calculating the average.
For example, suppose I am trying to score surveys on a 1to5 basis by asking about 5 specific attributes (such as cost, service, food quality, food quantity, decor,) of the diner's experience, and then calculate an "overall" score. Some survey respondents may omit one or more of those elements. I don't want to include one of the missing elements in the "overall" rating, as whatevernumber I imputed to that element would skew the overall calculation.
So, if the patron only answers 4 of the 5 elements, I would like the cell that is calculating the average to divide the total of the 4 answers by 4, not 5. Is there a way for the function in that cell to be told to disregard a blank cell that is supposed to be used as part of the average tally? |
|
| Back to top |
|
 |
RickRandom
Super User
Joined: 27 Jan 2006
Posts: 1082
Location: UK
|
| Posted: Tue Oct 17, 2006 8:41 am Post subject: |
|
|
| In Calc, the AVERAGE() function will ignore empty cells, so you shouldn't need to worry, but make sure you don't put 0 for a non-response. |
|
| Back to top |
|
|
Dale
Super User
Joined: 21 Feb 2005
Posts: 1440
Location: Australia
|
| Posted: Tue Oct 17, 2006 3:03 pm Post subject: |
|
|
| RickRandom wrote: | | In Calc, the AVERAGE() function will ignore empty cells, so you shouldn't need to worry, but make sure you don't put 0 for a non-response. |
Depending on how your data is being processed you might have zeroes where no response has been recorded. You can work around zeros if you have to:
=AVERAGE(IF(cell_range <> 0; cell_range; ""))
but finish editing with Ctrl+Shift+Enter (not enter. This makes it an array formula, which looks like
{=AVERAGE(IF(cell_range <> 0; cell_range; ""))
(Calc puts the bracer in for you)
_________________
Dale
To err is human, but to destroy your slippers in the process takes a real son of a bitch: Me!
OOo documentation from the source
http://documentation.openoffice.org
Guides, FAQ, How Tos |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
