Excel formula has stumped me! HELP!

[jonathanp]

ok im trying to create a sheet for my girlfriend. she has stomach problems depending on what she eats. ive made a sheet so she can put down everything she eats and give it a score out of five depending on how she feels the next day.

the problem is i need to add all these scored cells up and divide them by the amount of ACTIVE cells.
e.g.
A1 = Food name
B1 = Average cell score
C1 = Mon
D1 = tue
E1 = wed
F1 = Thur etc


A2 = Soup
B2 = ???????
c2 = 3 (the score out of five for how her stomach felt 5=good)
d2 = 2
e2 = empty cell she did not eat soup that day
f2 = 3


I need it so B2 takes c2,d2,e2,f2......all the way to KF2 adds them together, skips blank cells then divides them to find the average. Leaving me with an average score out of five.

The point of this is over time my girlfriend will be able to tell what foods react with her stomach.

Any help would be greatly appreciated.

Regards
jonathan

 

[DonkeyOte]

Re: Excel formular has stumped me! HELP!

use COUNTA or COUNTIF(range,">"&0) as your denominator

 

[jonathanp]

thanks but im a bit of an excel novice. Could you please give me the formula to put this in the field.

thanks in advance

 

[DonkeyOte]

B2 formula

=ROUND(SUM(C2:KF2)/COUNTIF(C2:KF2,">"&0),1)

Countif > 0 is pretty safe way of doing this.... the round assumes you want 1 decimal place in your avg -- change as necessary by altering the ,1 to ,2 for 2 decimals etc...

 

[jonathanp]

in short you are a god!
cheers fella

 

[Peter_SSs]

jonathanp

Welcome to the MrExcel board!

I would just use the built-in Excel function for AVERAGE - it already knows to skip blanks. The formula below can be adjusted to go to column KF (you must be using Excel 2007 to have that many columns) and then copied down as far as you want.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Food name	Average cell score	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue
2	Soup	2.67	3	2		3
3	Next Food	3.75		2	3				5	5
4
Food Scores