IF statement (with threshold?)

[saltwater]

Hello,

I need some help!
I have the formula below but would like to modify it to include a different cell somehow.
It should reference another cell saying not to go below a certain value.
The current formula does take B4 up or down 50% depending on the value but the problem lies when it goes down. It takes it down exactly 50%.
I need for it to abide by the threshold set in a different cell. For example, in a difference cell the new target would be 1.6 and it can take it down to a max of 50%, but it should NOT be a value that takes the new cell further than 1.6. The formula should result in a number to get as close to 1.6 as possible.
Any ideas? (I wish I could upload an example, but it appears that this is not possible)

=IF(B9>1.5,B4*(1-0.5),B4*(1+0.5))

 

[Marcelo Branco]

Sorry, but I do not understand what you want to do.

Consider the initial case where B4 = 8000 which should be reduced to 6500 as requested.
Changing the formula in D7 to =B7+D4-D6, we get

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Stock​

[/TD]
[TD]

16000​

[/TD]
[TD]

Stock​

[/TD]
[TD]

16000​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Purchase Order​

[/TD]
[TD]

8000​

[/TD]
[TD]

Adjusted Qty.​

[/TD]
[TD]

6500​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Average​

[/TD]
[TD]

14000​

[/TD]
[TD]

Average​

[/TD]
[TD]

14000​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Outgoing Orders​

[/TD]
[TD]

100​

[/TD]
[TD]

Outgoing Orders​

[/TD]
[TD]

100​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Ending Stock​

[/TD]
[TD]

23900​

[/TD]
[TD]

Ending Stock​

[/TD]
[TD]

30300​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

Target​

[/TD]
[TD]

1,71​

[/TD]
[TD]

Adjusted Target​

[/TD]
[TD]

2,16​

[/TD]
[/TR]
</tbody>[/TABLE]


We get in D7 an ending stock equal to 30300 which doesn't make any sense to me.

Maybe someone else can help you.

M.

 

[saltwater]

Yes, considering the initial case, the stock is now 30,300 which is fine, but why didn't D4 reduce all the way to 50%?
Now, I adjust D4 to, =IF(B9>1.5,MAX(D5*1.6+D6-B7,B4*(1-0.5)),B4*(1+0.5)), and leave D7 as =B7+D4-D6.
This scenario works for me.

Keeping the formulas stated above the same, only change B4 to 2800, the Adjusted Target is now 1.63, a 50% increase. I'm looking for only a 36% increase or 3800 in D4.
I'm looking for the formula in D4 to only produce a number that will not exceed 50% BUT to also not go over the Adjusted Target of 1.6 in D9.

 

[Marcelo Branco]

The initial stock was 16000; purchases = 6500 (adjusted from 8000); outgoing orders = 100.

For me the ending stock should be
16000+6500-100= 22400

With 30300 the result in D9 would be 2.16 instead of 1.6 as required (???)

As i said i don't understand how 30300 can be the ending stock

Hope someone can understand what you need.

M.

 

[saltwater]

Hello Marcelo Branco,

Thank you for your time and responses.
The situation is difficult to explain as the examples I have provided in the table are being tested in the actual file to validate compatibility.
The formula, =IF(B9>1.5,MAX(D5*1.6+D6-B7,B4*(1-0.5)),B4*(1+0.5)), works well for half of the scenarios.

May I kindly ask that you review the scenario below? This is the very last piece of the puzzle to finally complete this workbook that I have put over 100 hours into.
I would definitely be extremely appreciative and happy!

Keeping the formula highlighted in blue above, (and leave D7 as =B7+D4-D6) and change B4 to 2800, and B1 to 10000, the Adjusted Target is now 1.2, or 4200 in D4, a 50% increase. Which is perfect.
The formula attempted to reach 1.6, but only achieved a 1.2 Adjusted Target because of the 50% parameter.

Now, consider this scenario, change only B4 to 6000, (leave 10000 in B1) the result in D4 is 9000, which is too high, but now revise the formula to =IF(B9<1.5,MAX(D5*1.6+D6-B7,B4*(1-0.5)),B4*(1+0.5)) now the formula calculates a value of 6600 in D4, which is perfect for this scenario.

How can the formula be modified so that it may work with either of the two scenarios listed above?

Kind regards.

 

[saltwater]

Hi Marcelo Branco,

I have found a solution.
I placed both formulas that I highlighted in blue text from my last both in separate cells. (My actual file, AP9 and AP11)
I then placed a third formula, seen directly below, in a different cell as well. D4 is now looking at the third cell (Cell references below not relative to the information in this thread , but I think you can get the idea)
=IF(J18<B15,MIN(AP9,AP11),MIN(AP9,AP11))

J18=Original Target (B9)
B15= Target of 1.6
AP9:=IF(B9<1.5,MAX(D5*1.6+D6-B7,B4*(1-0.5)),B4*(1+0.5))
AP11: =IF(B9>1.5,MAX(D5*1.6+D6-B7,B4*(1-0.5)),B4*(1+0.5))

Thank you so much for your help!
Your assistance and generous time helped solve this problem.
Thank you again, best wishes. <b15,min(ap9,ap11),min(ap9,ap11))< html=""></b15,min(ap9,ap11),min(ap9,ap11))<>