IF & AVG functions

BTeeter

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!

Joanna Waligora

The start should probably be:

=IF(Today() < Date(2020,5,1), AVG(), VLOOKUP())

I can't help with the content/arguments in the AVG() and VLOOKUP() functions without knowing more about your data layout e.g. are your "previous 4 cells" in a row or a column? Also, depending on how you have things laid out, there's potential for added automation/cell references.