Context: In scheduling our podcast, we release four episodes per month. If there are 5 recording days in a month (Saturday), we record, but don't release that week, so we can build up a buffer.

The purpose of this function is to count the number of Saturdays alongside of the RecordingDates column (named range), setting the value to 1 if:

• The current row is the first data row below the column title OR
• The month changes
  • within the year OR
  • year rolls over

I have three custom functions for simplification:

1. ISFIRSTROW - Checks whether the value above is TEXT. Returns TRUE if so.

   =ISTEXT(Index(range,ROW()-1))
   

2. LASTROWUP - Returns the value of the previous row in the range of concern

   =Index(range,ROW()-1)
   

3. NEXTROWDOWN - Returns the value of the next row down in the range of concern

   =Index(range,ROW() 1)
   

Altogether, this is the formula:

   =IF(OR(ISFIRSTROW(RecordingDates),OR(MONTH(NEXTROWDOWN(RecordingDates))>MONTH(RecordingDates),AND(MONTH(LASTROWUP(RecordingDates))=12,MONTH(RecordingDates)=1))),1,LASTROWUP(WeekCount) 1)

Every one of those works separately. They also all work together if I start the fill from below row 2.

Where I'm stuck is that if I start this at row 2, I get this error:

Error
Function MONTH parameter 1 expects number values. 
But 'Recording Date (ten)' is a text and cannot be coerced to a number.

The ISFIRSTROW function returns TRUE when run on its own in row 2. So, to my understanding, the OR function in the IF should resolve to TRUE, and the IF should set the cell value to 1 and be done with it. I've tried a bunch of configurations of the formula, and am at wits end. I feel like I'm in a 70's product commercial screaming "There's got to be a better way!"

I know that the MONTH function needs a numerical input. What I don't understand is why that part of the formula is evaluating at all. Or, why I always have to think about what to do with the title if I want a function to start at the top.

A snapshot of the spreadsheet

CodePudding user response：

if you are in row 2 then ROW()-1 will bring data from row 1 and you have a header (text string) in row 1 so that's equal to error as it should because dates starts from row 2

try:

=IFERROR(IF(OR(ISFIRSTROW(RecordingDates), 
 OR(MONTH(NEXTROWDOWN(RecordingDates))>MONTH(RecordingDates), 
 AND(MONTH(LASTROWUP(RecordingDates))=12,MONTH(RecordingDates)=1))),1,LASTROWUP(WeekCount) 1))