Is it possible to set a formula to subtract the next row that meets a specified criteria?

Hollie Green

I want it to look for the name john and subtract the last number John entered from his most recent entry. My form is set up to have the entries come in at the top of the sheet. Any ideas on a formula to use?

Paul Newcome

Does this work for you?

=IFERROR(Number@row - INDEX(COLLECT(Number:Number, Name:Name, @cell = Name@row), 2), "")

Hollie Green

This formula makes everything subtract from the 2nd to the last entry made by the person. So in my example it is subtracting all of John's entries from 4 regardless of where they fall within my sheet and Jane's entries are all being subtracted from 5

Hollie Green

@Paul Newcome If it helps I have a created field I can reference for when the submission was made I just can't figure out how to get it to calculate the next row I need to subtract it from

Hollie Green

I figured it out!

I created some helper columns to turn my Created date into a number I could use as a Max value. Posting for anyone else looking to do this below is how I got it to work. There may be an easier way but I couldn't figure out an easier way. Most of the formulas was turning the created by into a value that would work for both before and after noon as well as from one day to the next day.

Helper columns - AM/PM, Hour, Minute, Date, Military Time, Time Conversion, Max

Formula for AM/PM

=IFERROR(RIGHT(Created@row, 2), "")

Formula for Hour

=IFERROR(IF(MID(Created@row, (FIND(":", Created@row) - 2), 2) = 12, 0, MID(Created@row, (FIND(":", Created@row) - 2), 2)), "")

Formula for Minute

=IFERROR(MID(Created@row, FIND(":", Created@row) + 1, 2), "")

Formula for Date

=DATEONLY(Created@row)

Formula for Military Time

=IFERROR(IF([AM/PM]@row = "AM", Hour@row + ":" + Minute@row, VALUE(Hour@row) + 12 + ":" + Minute@row), "")

Formula for Time Conversion

=VALUE(IF(LEN([Military Time]@row) = 5, LEFT([Military Time]@row, FIND(":", [Military Time]@row) - 1) + RIGHT([Military Time]@row, FIND(":", [Military Time]@row) - 1), LEFT([Military Time]@row, FIND(":", [Military Time]@row) - 1) + RIGHT([Military Time]@row, FIND(":", [Military Time]@row))))

Formula for Max

=VALUE(YEAR(DATEONLY(Date@row)) + "" + IF(MONTH(DATEONLY(Date@row)) < 10, "0") + MONTH(DATEONLY(Date@row)) + IF(DAY(DATEONLY(Date@row)) < 10, "0") + DAY(DATEONLY(Date@row)) + IF([Time Conversion]@row < 1000, "0") + [Time Conversion]@row)

Final formula to get the results I needed

=IFERROR([Number]@row - INDEX(COLLECT([Number]:[Number], [Name]:[Name], @cell = [Name]@row, Max:Max, @cell < Max@row), 1), "")

Paul Newcome

@Hollie Green My apologies. I inadvertently skipped a step.

The first step is to actually insert an auto-number column with no special formatting. Then the final formula would be:

=IFERROR(Number@row - INDEX(COLLECT(Number:Number, Name:Name, @cell = Name@row, [Auto-Number]:[Auto-Number], @cell <= [Auto-Number]@row), 2), "")

Saves from having to try to parse out the time from the Created column.