Marquette Method — First 6 Breastfeeding Cycles — Technical Notes

This page provides information about how the Marquette First 6 Breastfeeding Cycles 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 breastfeeding Marquette NFP Method:

  • Fertility begins on the earliest day specified by any rule below.
  • In cycle 1, fertility begins on day 10.
  • In cycle 2, fertility begins on day 9.
  • In cycle 3, fertility begins on day 8.
  • In cycle 4, fertility begins on day 7.
  • In cycles 5 and 6, fertility begins on day 6.
  • If you encounter a H(igh), the fertile window begins.
  • The fertile window continues 5 days past peak (PPHLL) and ends on the 6th day.
  • This chart should only be used for (up to) the first 6 breastfeeding cycles. After that, go back to cycle 1 on the normal Marquette Method chart.

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 isn’t needed for this chart. Unlike the normal chart, we already know when the upcoming fertile window will begin.
  • Fertile Window Start is hard-coded based on the rules above.
  • 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, this is just the difference between the fertile window start and our current cycle day. If we’re post-peak, we add the estimated days remaining (based on average cycle length) to the upcoming fertile window start day. 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).