# Constant number divided depending on IF Parameters

Copper Contributor

# Constant number divided depending on IF Parameters

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.

3 Replies

# Re: Constant number divided depending on IF Parameters

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?

# Re: Constant number divided depending on IF Parameters

The Value in I2 is the base number, however this will change based on data on another sheet.

Essentially what you're suggesting sounds correct. I2 will remain at Total Sales of 21,010
Other cells along that same row, will update depending on Yes or No, and the remaining cells will automatically adjust value down if other cells have increased.

Am I able to insert that code into Office365 or is that only possible on desktop version?

# Re: Constant number divided depending on IF Parameters

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!