average question

[celticdog]

Hi folks

I have creted a excel sheet to track the blood glucose level of my diabetic dog. He can have up to 12 readings per day, so ther are 12 coulums named reding 1 through to 12. The there is a daily avarge. However this is where I am lost, if all the daily readings a blank then I get a DIV/0 error, now this isn't that much of an issue. But I have a weekly, monthly and 14 avrage so when I email it to the vet every week they can see the on going avarage. At the moments the forumla is =AVERAGE(C2,E2,G2,I2,K2,M2,O2,Q2,S2,U2,W2,Y2)

How can I get it ignore blank cells?

I have attached the spreedsheet too.

Any help would be great

Jim

[Ron Coderre]

Using your posted workbook, I recommend these changes....

This formula calculates the average for the day

Please Login or Register  to view this content.

Notes:
• The COUNT function counts the numbers in the referenced range(s)
• the Average function ignores text and blanks

Copy that formula into each Daily Average cell.

This formula calculates the weekly average

Please Login or Register  to view this content.

That structure can be used in the other weekly, monthly, and yearly
averages (after adjusting the range references).

Is that something you can work with?
Post back if you have more questions.

[Ron Coderre]

Is it possible that some tests may not be performed? (forgotten, maybe)

If yes, then...
• You cannot simply average the daily averages and get the correct
average for the week. That only works if ALL days have the same
number of tests.

• The formulas I posted allow for some tests to be missed, while still
calculating the correct daily and monthly averages.

[celticdog]

Hi Ron

Thanks for the help, that works a treat.

"Is it possible that some tests may not be performed? (forgotten, maybe)"
Basicaly everyday could have diffrent tests.. there should only be 2 tests when his blood suger is under control, but at the momnet it's not so there can be any amout. Also I have to do a monthly curve, which menas a test every two ours to see dips etc, so again these days would show more tests.