Marquette Method NFP Chart — Technical Notes

This page provides information about how the Marquette NFP Chart is implemented in Excel. You don’t need this information to use the chart, but some people might be interested in understanding how the chart works so they can help check for bugs or make their own modifications.

Rules

This Marquette Method Chart in Google Sheets is based on some of the basic rules from the Marquette NFP Method:

  • In the first 6 cycles, the fertile window begins on day 6.
  • After the first 6 cycles, the fertile window begins 6 days before the earliest peak of the previous 6 cycles.
  • If you encounter a H(igh) or P(eak), the fertile window begins.
  • The fertile window continues 5 days past peak (PPHLL) and ends on the 6th day.

Data Flow

To understand how this spreadsheet works, it’s probably best to start by understanding how data flows through the spreadsheet.

  • The user inputs data in the Chart sheet.
  • The chart is transposed into the first 5 columns of the Data sheet.
  • Columns G-F in the Data sheet calculate whether or not that day is a fertile day.
  • The Calculations sheet grabs today’s info out of the Data sheet.
  • The Summary sheet turns the Calculations sheet into easy to understand English.
  • The Fertility Window in the Chart is populated based on the Fertile column in the Data sheet with conditional formatting.

Data

The Data sheet represents the data from the Chart in a vertical format where each row represents an entry in the chart, and columns represent different values for that day. This us useful because it’s much easier to perform calculations in Excel (or Google Sheets) when the data is organized with one entry per row. This Data tab begins with the information from the chart (in the first 6 columns) and builds toward knowing if that day is fertile or not through a series of calculations performed on each row.

  • Columns A-F are simply transposed data from the Chart tab. The INDEX formula you see is used to perform the transpose while moving ahead to the next cycle if we’re beyond the last column.
  • Chart Row help us know which cycle we’re on. This is used as part of other calculations like Avg Pre-Peak length. It is calculated by adding 1 to the previous row if we’re on day 1 of a new cycle.
  • Cycle Num is mostly for reference – Chart Row is actually more useful in our calculations since we can’t look at data older than Chart Row 1. It is calculated by adding the cycle number of chart row 1 (from the Chart tab) to the chart row.
  • Phase is always either Pre-Peak or Post-Peak. This is useful for knowing stats like the avg pre-peak length, which is useful in some of our “probable days ‘til X” calculations. It is set to “Pre-Peak” if we’re on day 1, “Post-Peak” if the monitor reads “P”, otherwise the phase of the previous day.
  • Avg Length is the average length of the previous 6 cycles. This is useful in some of our “probable days ‘til X” calculations. It is calculated by counting rows (from the Data tab) where the Cycle Num is one of the previous 6 cycles, and dividing by 6 (or the total number of cycles, if in the first 5).
  • Avg Pre-Peak is the average pre-peak days in the previous 6 cycles. It is calculated identically to Avg Length, but we only count days where the Phase is “Pre-Peak”.
  • Days Post-Peak is useful for our “probable days ‘til X” calculations. It is calculated as 0 if we’re in the pre-peak phase, otherwise by adding 1 to the previous count.
  • Earliest Peak is our most complex calculation and is also a critical part of the Marquette Method’s fertility prediction. To calculate this number, we take the MIN of six different numbers representing the peak of one of the previous 6 cycles. We default each of these 6 calculations like IF(H2 <= 1, 12, which leads us to begin fertility on day 6 by default unless one of the cycles has an earlier peak than day 12. (The 12 is because fertility begins 6 days before the earliest peak). The SUMIFS bit gets us the day of Peak in the cycle we’re examining.
  • Fertile? is a boolean column representing the fertile window. The fertile window begins in the pre-peak phase and ends in the post-peak phase, which is the reason for the IF(I2 = "Pre-Peak" in our calculation. If we’re in the first 6 cycles, the fertile window begins on day 6. (This is accounted for with a default earliest peak of 12.) Otherwise, the fertile window begins 6 days before the earliest peak or if you get an H or P on the monitor. The fertile window ends on the 6th day after a peak.

Calculations

Today Is is set to TODAY(), but you can manually enter any date here to test the calculations for any other date. Based on that cell value, the next several rows grab “today’s” data from the Data sheet. We calculate Post-Peak Days This Cycle by counting entries in Data!I:I containing Post-Peak with the correct cycle number. (This will always be calculated, but will be 0 if we’re in the pre-peak phase.)

Then we calculate Probable Days ‘til Sex. If we’re in the pre-peak phase, this is an estimate, and we calculate it based on the average pre-peak length, less our current day, plus 6 days post-peak. If we’re in the post-peak phase, the calculation is exact because we know the fertile window ends 6 days after peak. We also calculate a Remaining Sex Days value, which will be used to estimate the time before the next fertile window starts. If we’re in the pre-peak phase, we estimate this value based on the avg length of the pre-peak phase, less 7 days (because there are 0 days remaining on the last sex day), less our current cycle day. If we’re post-peak, we add the estimated days remaining (based on average cycle length) to the average pre-peak length less 6 days. Depending on whether we’re in the fertile window, we’ll show either Days ‘til Sex or Remaining Sex Days in the summary.

Demo Mode

I use Demo Mode to demonstrate what the spreadsheet can do, as well as to test the spreadsheet with realistic data. A “Demo Mode” sheet has the first ~6 cycles filled in with data and the start dates calculated backwards to make it look like the last entry was made on today. Normally, the start date of each cycle is blank. In “Demo Mode”, however, we’ll set the start date of the current cycle to TODAY() - 14 (subtract enough days to make “today” the last entry), and we’ll set the previous start days by iteratively subtracting each cycle length. To take a sheet out of demo mode, delete all the start dates and clear the user-entered data (bleeding, monitor, OPK, intercourse).