0

I'm creating a running target from a quarterly Total divided by known trading days

Current formula in I4 is =IF(H3<>"",I3+O$30,"")

H3 = Yesterday's Sales Numbers
I3 = Yesterdays Running Target
O30 = Required Daily Run Rate

Column C is a Date Column

I want the running target to show in I4 when it is Today (currently it shows a day in advance, so showing today the 2-Feb)

enter image description here

Improve this question

4 Answers 4

Reset to default
0

  • alternative aproach:

    =IF(AND(C4 < TODAY()+1, H3<>""), I3+O$30, )

Improve this answer
0

  • not sure how your dates are generated but one way would be to use conditional formatting and setting font color and cell background to white:

Improve this answer
3
  • Won't that just take an amount of 1 of the Required Run Rate?
    – woodie OS
    Feb 2, 2019 at 14:15
  • Have added an image for you now. In short, trying to get the data in column I not to show until that day arrives (currently 3-Feb where I am and data already showing for 4-Feb)
    – woodie OS
    Feb 2, 2019 at 23:02
  • To give you some context around the dates, I have done a year in advance and use NETWORKDAYS.INTL to calculate the actual trading day per location. I just need to stop the next days Run Rate showing (as it adds to my current week count and wrecks the %). So the data in Column I doesn't need to show until there is data in Column D - Column H
    – woodie OS
    Feb 3, 2019 at 1:47
0

  • generating a column of dates with a formula:

    =ARRAYFORMULA(TO_DATE(ROW(INDIRECT("A"&DATE(2019,2,1)):
                          INDIRECT("A"&TODAY()))))

  • then you can do:

    =IF(AND(C4<>"", H3<>""), I3+O$30, )

Improve this answer
-1

Solved using the following formula:

=IF(AND(C4 < TODAY()+1, H3<>""), I3+O$30, )
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.