OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

disregarding cells with no data entered when calc'g avg.s?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
rnrasmus
Newbie
Newbie


Joined: 17 Oct 2006
Posts: 1

PostPosted: Tue Oct 17, 2006 7:54 am    Post subject: disregarding cells with no data entered when calc'g avg.s? Reply with quote

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
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Tue Oct 17, 2006 8:41 am    Post subject: Reply with quote

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
View user's profile Send private message
Dale
Super User
Super User


Joined: 21 Feb 2005
Posts: 1440
Location: Australia

PostPosted: Tue Oct 17, 2006 3:03 pm    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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