Collect record for previous day to Calculate variance

Shile
Shile
edited 01/15/22 in Formulas and Functions

Main problem: I am attempting to collect the record of Daily absent for the previous day, I get incorrect argument error. I could use any help.

My end goal is to use the previous day record against the daily absent record to get the variance in numbers and percentage. I would appreciate any other intuitive way of doing that, if there is any.

Thanks


Tags:

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Shile

    =JOIN(COLLECT([Daily Absent]:[Daily Absent], Date:Date, Date@row-1))

    Unless you need the range to be confined to rows 1 through 9 you don't need the row numbers. This will keep your range dynamic as the column grows. The Join delimiter is an optional term so I didn't include in my formula above as you are only pulling in one value.

    You post suggests you will ultimately 'do math' with the value in the [Previous Days Absent No] column. Consider changing the 'JOIN' to a 'MAX' so that the data returned has the characteristics of a Number vs the text characteristics the JOIN produces. If you do use MAX then you will not use a delimiter.

    cheers

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Shile

    =JOIN(COLLECT([Daily Absent]:[Daily Absent], Date:Date, Date@row-1))

    Unless you need the range to be confined to rows 1 through 9 you don't need the row numbers. This will keep your range dynamic as the column grows. The Join delimiter is an optional term so I didn't include in my formula above as you are only pulling in one value.

    You post suggests you will ultimately 'do math' with the value in the [Previous Days Absent No] column. Consider changing the 'JOIN' to a 'MAX' so that the data returned has the characteristics of a Number vs the text characteristics the JOIN produces. If you do use MAX then you will not use a delimiter.

    cheers

    Kelly

  • Ordep
    Ordep ✭✭

    Hi Kelly, thank you for your post.

    But how do I solve the situation where there is no "Date@row-1"

    Eg.: The date goes directly from Dec 12th to Dec 18th. This way Date@row-1=17th (of which there is no register.

    I have a column of dates that, but sometimes there gaps in those dates and corresponding data in the data column.

    I nevertheless want to automatically (formula column) calculate the diference between the most recent regsiter of data with the previous register of data (that is not necesarily the day before the current register date, but the most recent date).

    Kind regards and a great weekend

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 05/18/22

    Hey, sorry for the delay

    Will this formula work for you? It would replace my original formula

    =INDEX([Daily Absent]:[Daily Absent], MATCH(MAX(COLLECT(Date:Date, Date:Date, <Date@row)), Date:Date, 0))

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!