Variance Formula

ldavenportHAP

I need assistance creating an formula to compare this months "Accounts Per Day w TOOQL" number vs last months to see if the number has increased or decreased. Is there a way to do this the way i have my sheet set up? Example with the icons i am looking to utilize

James Keuning

There must be something missing here, how do you know which record to compare with which record, month over month? March is missing a record, how do you know which one is missing?

If it's always the same seven values, and they are always in the same order, you can work with that, but the fact that a month has fewer records means that it's not the case.

James Keuning

Here you go. I made an assumption that there is some way to match each record in the current month to the appropriate record in the previous month.

https://app.smartsheet.com/b/publish?EQBCT=0d07d37f2d2a4333ae2a210ccb6284f3

Here is an explanation of each field, hide these.

Parent Month: This formula get the current month for the current record by looking at the parent value.

Acct+Date: Creates a unique identifier by concatenating the account number and the date

Lookup Previous: Looks at all of the previous records in the previous months and find the latest. Basically, get me the MAX date for every record that is less than this date.

Match Row Previous: Uses MATCH to find the row number for the record that you just identified

Previous Value: Uses INDEX to use MATCH to find the value that you are subtracting

Final Answer: Uses IF to handle the comparison and the result

ldavenportHAP

Thank you so much @James Keuning i really appreciate it!

I am copying what you sent me but i cannot get the "Match Row Previous" to populate anything. i believe i copied exactly

=IFERROR(MATCH([Lookup Previous]@row, [Acct+Date]:[Acct+Date], 0), "")

James Keuning

If you want to share it to me, I can take a look. Scrub the confidential stuff out.

jmkeuning@hotmail.com

ldavenportHAP

@James Keuning i did send it to you, did you receive it? i realize i probably should have replied here also :)