Calculating Pace using Days Passed

[skling184]

I am looking for a way to use the number of working days passed to figure sales pace on a daily basis. I have the formula already to use (total sales) / # of working days passed * # of selling days in the month. I added a helper field for both areas to represent number of days passed, but not sure if this is the most appropriate way? Also I need this to update on a daily basis and the trouble I am having is data needs to be pulled from the next days cell. For example on 4/20 The formula is =(B18/B29) *26 , however the next day on 4/21 it would have to be changed to =(B19/B29) *26. Basically the reference cells need to change automatically each day and I do not know if this is possible.

 

[Dave87]

can you take the screenshots again but inlcude the column and row letters/rows so i can see which cells you are referring to

Id imagine using the $ symbol before =(B18/B$29)*26 would solve your problem but cant be sure without knowing which columns/rows you refer to

 

[skling184]

Dave thanks in advance for the help! I will include a better screenshot for you. Below is an example of the formula I am using now and a better description of what I need to accomplish.


=(total / #ofdayspassed) * #ofworkingdays



=(B19/B31) *B30 ex. current formula but each day it needs to reflect a different reference cell.

=(B20/B31) *B30

=(B21/B31) *B30

=(B22/B31) *B30 and so on as the days progress.



Essentially, I need the formula to pull data from the bottom row each day, however there are 2 sides of information using a different number of working days.

Hopefully this makes sense.

Thanks again!

 

[skling184]

Hoping I am not asking for the impossible here. It seems like a simple enough concept to me anyway. Maybe someone else has an answer??

 

[Tetra201]

Maybe

=VLOOKUP(TODAY(),A2:B27,2,1)/B31*B30

 

[Dave87]

=VLOOKUP(TODAY(),A$2:B$27,2,1)/B$31*B$30
try adding the $ sign to lock-in the references

 

[skling184]

Thanks Tetra and Dave! I tried both of the formulas you suggested. I am still getting a zero for the pacing number which obviously isn't correct.

Any ideas as to what the issue might be?