Feb 10 2024 01:52 PM
I could be completely off with this, but I'm trying to do something which in my head is very simple, but my brain will not process the formula.
Essentially, in cell I2, I have a number that is prepopulated. In the cells B4:H4 I currently have a very simple division of 7, from I2, to give a daily sales target. However in cells B3:H3 I have the option to select Yes or No. When Yes is selected I want the numbers in B4:H4 to adjust accordingly, so that may mean that the sales target is requested higher for the one day with the Yes, but then automatically lowers for the remaining 6 days.
I've tried some IF formulas, and best I've done so far is create some background data sheet to facilitate a sum, but that involves manually entering and adjusting % to get the desired out come and I'm hoping that a simple drop down yes or no will make life easier for my colleagues.
Feb 10 2024 03:14 PM
If your base value was the same for each day but the target had some days 50% higher than others, the formula
= LET(
modified, IF(option="yes", 1.5, 1) * base,
modified / SUM(modified)
)
would scale the values where the option is set, but then renormalise to give the same weekly target. Is this what you require?
Feb 10 2024 03:37 PM
Feb 11 2024 04:45 AM
The code will work with 365 online but, without Name Manager, it will be difficult to prepare. I only use defined names, never direct cell references, so I have simply avoided the online version despite its strengths for collaborative development. It is probably time I gave it another go!