Calculate progress towards a goal

Christina Reid

I'm looking at two things - one to highlight when a goal is not on target to complete by a date. The second is note a specific amount in a cell on a certain date.

To clarify on the first - I need a formula that will tell me if the number in the Target AUA cell is not going to be meet by the date next to the 90 days cell (in this example - 10/30/20). Basically I want it to highlight that if we need to meet $9.8 million over 90 days, and the Complete AUA on any given day is X, are we averaging towards that goal?

On the second - I also would like to capture what the Complete AUA is at the 60 day mark and have it stored on the sheet. I'm not sure that is possible. Right now I have a daily report of the figures emailing to me and I think I may need to just manually locate the report from that day and pull the numbers. I appreciate any other creative ideas though!

Paul Newcome

Ok. So to go 90 days back from the 90 days mark to establish a start date, we would use...

=[Dates]9 - 90

Now that we have a start date, we can determine how many days have passed since then.

=TODAY() - ([Dates]9 - 90)

Once we divide the dollar amount by that many days we will have the current average.

=[Complete AUA]1 / (TODAY() - ([Dates]9 - 90))

Now we need to pull the needed average by dividing the target amount by the 90 days.

=[Target AUA]1 / 90

Then we can say that if the current average is less than the target average, flag.

=IF([Complete AUA]1 / (TODAY() - ([Dates]9 - 90)) < [Target AUA]1 / 90, 1)

To highlight you would set up conditional formatting based on the flag being present.

To "lock in" the 60 day value, you would need a few things. First you will need something unique to each row. Next you will need a second sheet that has all of the same columns as your main sheet.

You are going to want a cell on the row you want to capture that contains the date for 60 days.

Then you would set up a copy row automation to be triggered on that date.

Finally you would use cross sheet references to look at the second sheet and pull that data back.

=INDEX({Copy Sheet Complete AUA Column}, MATCH([Unique ID Column]1, {Copy Sheet Unique ID Column}, 0))