IF & AVG functions
I'm looking to create a report of averages and actuals to help predict spending. I'd like to create a function that does the following:
- Displays the average spend for YTD if today's date is less than the 1st of current month
- Displays the actual spend for the month if today's date is greater than the 1st
I'm starting with, for example:
- =IF([Today's Date]1<[5/1/20], =AVG(previous 4 cells), =VLOOKUP(Supplies from May Actuals))
- If today's date is less than May 1, display average Jan-April totals, if greater than May 1, display actuals from another sheet
So the idea is to be able to project average spending over the next several months and project cost-savings based on current spending.
I'm getting errors trying to create this formula and looking for some help!
Thanks in advance!
Help Article Resources
Check out the Formula Handbook template!