Caculating a running average based on several criteria

**Mercules** · 06-23-2010, 12:45 PM

I am currently working on a spreadsheet that calculates a thermal shift based on a series of data. The data that is input by the user is a body temperature for each day of the month. A chart is automatically generated and updated as the user inputs the temperatures, and at some point during the month there will be a "thermal shift". This shift occurs when:

1)a certain cell value directly above the day that is being looked at does NOT contain a 1
2)it takes the average of the past 6 days before the current days temp as long as none of those 6 days contain a condition specified in condition 1.
3)it then checks to see if the current day's temperature is above .4 degrees higher than the average calculated in 2) and if yes, it is the thermal shift day and is plotted. If it is not, then nothing is done and we wait until the next day to see if the thermal shift happens.

The thermal shift correlates to a woman ovulating and the condition 1) is if there is any menses during that day. It is a sheet to visually update and help to figure out when/if the woman is fertile. I would like to do this with simple code (hopefully not a macro that has to keep being run). That way, it should work on mac and windows excel as well as different versions. The instantaneous plotting is visual feedback that can really help to show the fertile periods of a woman's cycle.

I would really appreciate some help in this and can upload a file of the sheet that I have at my house later tonight if someone is interested. Thanks so much!

-Brett

**ChemistB** · 06-23-2010, 03:17 PM

Kinda hard to imagine what you're looking for without an example spreadsheet (Go advanced>Manage Attachments).

See attached for what I came up with.
row 1 is day of month
row 2 is where your "1"s come in. (I randomly threw in a few ones)
Row 3 is your data.
Row 4 is a formula to pull data (thermal days) which need to be graphed.

In G5 (for example)
=IF(OR(COUNTIF(A3:G3,1)>0,AVERAGE(A4:F4)>=G4-0.4),0,G4)
I took your conditions 1 and 2 and what it says (how you stated it) is that there can't be a 1 for the last 7 days. (COUNTIF(A3:G3,1)>0
AVERAGE(A4:F4)>= G4-0.4 gives your condition 3.
I also conditionally formatted Row 4 to have 0's in white font so it looks nicer. How far off am I?

I didn't use Macros as not all MAC versions are Macro compatable (I hear).

**Mercules** · 06-24-2010, 07:39 AM

Thanks for the quick reply ChemistB,

I uploaded a copy of my spreadsheet so that you can see exactly what I am talking about. Your formula is a great starting point; there are a few more criteria that I need to make you aware of.

Firstly, because ovulation only occurs once per cycle (it is possible to have multiple, but they always happen directly after the first and they are rare), we only want the first thermal shift calculated and plotted.

Secondly, body temperature can vary significantly due to stress, sleeping pattern, and other factors so we need to be able to have the user delete a temperature for a given day if need be without messing up the entire plot. In some cases, there may be even two or more deleted temps in a row.

The average temp that is calculated then must be of the last 6 temperatures recorded (as long as none of those overlap the end of the period). Let's say that you are currently 7 days past the end of menses and also that the day following the end of bleeding was a bad temperature. We throw that data point out, and the average is calculated from the last 5 days- only because we can't count before the end of bleeding, and we don't have 6 days after yet.

You should be able to play around with the chart and delete some points to see what I mean. I had it working for the most part, but I didn't know the part about not using the temperatures during the bleeding period at all before a few weeks ago. I plan on hiding rows 43 through 51 when I am finished and have a nice way to chart these for our records! It is likely to be shared with many other people when it is finished, so you will be helping to have a great impact. Thanks for all of the help and ask more questions where I was unclear!

-Brett