Is it possible to set a formula to subtract the next row that meets a specified criteria?
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?
Answers
-
Does this work for you?
=IFERROR(Number@row - INDEX(COLLECT(Number:Number, Name:Name, @cell = Name@row), 2), "")
-
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
-
@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
-
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), "")
-
@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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!