Auto Calculating Changing Cell Reference
Posted on 2016-09-14
I am working with an excel sheet that my sales staff updates daily to help record our sales numbers. On the sheet is our monthly goal, our total this far, and a break down of what each day of the month is worth (3.33%) and how that figures in terms of dollars (our goal divided up into 3.33% increments for each day of the month) This way we can compare where we are in the month to where we need to be pacing toward our sales goal.
(We also have a box that calculates how many days are left in the month. )
What I want to do is create a box that offers us a daily updating auto calculation of how far +/- we are from pace to goal. This requires a formula that changes the cells it references every time someone enters the sales numbers because now we would be looking at numbers one row down from the previous day. I am fairly Excel literate, but I don't know the formula for this changing/updating reference.