Hello, all.  I hope you're enjoying your summer.

 

I'm trying to add trailing subtotal average fields to my wife's insulin database (she has fields for breakfast, lunch, dinner and bedtime), but sometimes she doesn't make a record of her blood sugar reading.  How can i create a calculation that will average a field without counting blank fields, which would of course skew the average downward?

 

Thanks, and have a great day.

 

Kevin 

Your question is not quite clear. What exactly do you want to average? It seems you have a record for each day, with 4 fields for different times of day. I am not sure that's the best structure to have, but let's put that aside for a moment. Do you also want 4 average values? If so, use summary fields. A summary field defined as Average ignores empty and non-numeric values.

Thank you, Comment.  I did not know that summary fields ignore empty values.  And yes, I would need four separate summaries.

 

Sorry it took so long to reply...I just returned from vacation in Aruba.  Wish I was still there.

 

Have a nice weekend.

 

Kevin

And yes, I would need four separate summaries.

 

If you like, you could use a single repeating field with four repetitions to record the values and a single summary field - with the 'Summarize repetitions' option set to [individually] - to provide the four summaries. Use a second summary field with the 'Summarize repetitions' option set to [All together] to provide the overall average (beware of averaging the averages).