Variance Formula
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
Answers
-
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.
-
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.
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
-
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), "")
-
If you want to share it to me, I can take a look. Scrub the confidential stuff out.
jmkeuning@hotmail.com
-
@James Keuning i did send it to you, did you receive it? i realize i probably should have replied here also :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!