[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

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?
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.
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
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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