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

0
  • alternative aproach:

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

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:

  • Won't that just take an amount of 1 of the Required Run Rate? – woodie OS Feb 2 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 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 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, )

-1

Solved using the following formula:

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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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