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]

Could you provide some examples?
Something like
Target = 1.6
B9 = 1; B4 = 2; Desired result = ?
B9 = 2; B4 = 2; Desired result = ?
etc

M.

 

[DataBlake]

(I wish I could upload an example, but it appears that this is not possible)

you can upload a worksheet to any trusted file hosting site (google drive, onedrive, dropbox), but people may not want to download a file from someone they don't know.
So try using the Table Maker inside Mr.Excel just click "go advanced" and use the table properties to demonstrate multiple examples.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Formula[/TD]
[TD]Target[/TD]
[TD]Threshold[/TD]
[/TR]
[TR]
[TD]?[/TD]
[TD]1.6[/TD]
[TD].4[/TD]
[/TR]
[TR]
[TD]?[/TD]
[TD]1.6[/TD]
[TD].5[/TD]
[/TR]
</tbody>[/TABLE]

something like that?

i'm still confused by what you mean by the following quoted statement:

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.

1. What is "it" theres too many "its" i don't know what "it" is
2. if the target is 1.6 why are we trying to get as close to 1.6 as possible? or are you saying the threshold is 1.6 and we have to get the target to the threshold and display the percentage?

 

[saltwater]

Hi BlakeSkate,

Your'e absolutely right, too many "its" and I apologize.

BlakeSkate, Marcelo Branco, and anyone else...
Please reference the table below where the text "Stock" is in Cell A1, and the text "Adjusted Target" is in D9.
Here are the formulas; Every other cell simply contains text or values, no formulas.
Cell B7: =B1+B4-B6
Cell B9: =B7/B5
Cell D4: =IF(B9>1.5,B4*(1-0.5),B4*(1+0.5))
Cell D7: =D1+D4-D6
Cell D9: =D7/D5

Essentially, I have an opportunity to adjust the purchase order (In this example, 8,000 pcs as seen in B4) up or down 50%) (Opportunity to adjust from Columns C:D)
D4 formula recognizes that the original target of 1.71 (CellB9) is higher than 1.5 so the formula reduces 8000 pcs by 50% resulting in 4000 pcs as seen in D4.
I would like for the formula to reduce by a maximum of 50% but not to go below the new target of 1.6.
Cell D9 has the same formula as Cell B9 meaning that if you replaced 4000 in D4 with 8000 it would result in 1.71 as well. (Same as B9)

BlakeSkate, the Adjusted Target is in fact 1.6 so please disregard my original statement "as close to 1.6 as possible"
No need for the spreadsheet to display the percentage difference.

The value I seek in D4 is 6,500
Explanation: Although Cell D4 has an opportunity to reduce B4 by 50% the cell only reduces by 23% in order to meet the 1.6 target.

I hope this further explains my objective, and I'm happy to answer any additional questions.
Thank you in advance!

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Stock[/TD]
[TD]16000[/TD]
[TD]Stock[/TD]
[TD]16000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Purchase Order[/TD]
[TD]8000[/TD]
[TD]Adjusted Qty.[/TD]
[TD]4000[/TD]
[/TR]
[TR]
[TD]Average[/TD]
[TD]14000[/TD]
[TD]Average[/TD]
[TD]14000[/TD]
[/TR]
[TR]
[TD]Outgoing Orders[/TD]
[TD]100[/TD]
[TD]Outgoing Orders[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Ending Stock[/TD]
[TD]23900[/TD]
[TD]Ending Stock[/TD]
[TD]19900[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Target[/TD]
[TD]1.71[/TD]
[TD]Adjusted Target[/TD]
[TD]1.42[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

If you want D9 to always be greater than or equal to 1.6, maybe ...

D4
=MAX(D5*1.6+D6-D1,IF(B9>1.5,B4*(1-0.5),B4*(1+0.5)))

Hope this helps

M.

 

[saltwater]

Hi Marcelo Branco,
Thank you for your response, the formula is working. However it now appears that I should have mentioned that D4 should not exceed 50% as well. I'm terribly sorry about that. (I thought the latter part of the formula, B4*(1+0.5)) would have taken care of that)
Is there a way to modify the formula so it does not exceed 50%? The issue appears to lie only when there is low stock, I could put 100,000 in D1 and D4 has only reduced to a max of 50%, which is great.

For example, if D1 has the value 9000, it results in a 69% change. (The target 1.6 in D9 remains 1.6, which is great as well, but if there is a way to have D4 to not exceed 50% would be beyond fantastic!)

 

[Marcelo Branco]

Hi Marcelo Branco,
Thank you for your response, the formula is working. However it now appears that I should have mentioned that D4 should not exceed 50% as well. I'm terribly sorry about that. (I thought the latter part of the formula, B4*(1+0.5)) would have taken care of that)
Is there a way to modify the formula so it does not exceed 50%? The issue appears to lie only when there is low stock, I could put 100,000 in D1 and D4 has only reduced to a max of 50%, which is great.

For example, if D1 has the value 9000, it results in a 69% change. (The target 1.6 in D9 remains 1.6, which is great as well, but if there is a way to have D4 to not exceed 50% would be beyond fantastic!)

See if this does what you are asking
D4
=IF(B9>1.5,MAX(D5*1.6+D6-D1,B4*(1-0.5)),B4*(1+0.5))

But be aware that in some cases the result in D9 will be less than 1.6.
For example, type 3000 in B4 - in this case D4 will be equal to 4500 (50% increase) which will bring D9 to a value of 1.46 (less than 1.6)

Is this really what you want?

M.

 

[saltwater]

Hello Marcelo Branco,

Thank you for your continued support.
Yes, your proposed formula is great!
While the Adjusted Target is indeed 1.6, the parameter of 50% limits D4 to not go any higher.
1.46 is acceptable.

What about this scenario? Keep 3000 in B4, but place 18900 in D1. (Which was the ending stock in B7)
The Adjusted Target is now 1.66, it appears D4 went all the way to 50%, I'm okay with the figure being below 50%, 50% is simply the max the figure can go to. (Also, just to mention, the minimum is also 50% but can be in between as well)

The result in D4 should be 3600, only a 20% increase to achieve the target of 1.6

Edit: I'm okay with adding helper cells if needed, if that will achieve the desired result.
I also have the formula listed below in D12, in order to check the percentage difference.
=(D4-B4)/B4

 

[Marcelo Branco]

What about this scenario? Keep 3000 in B4, but place 18900 in D1. (Which was the ending stock in B7)
The Adjusted Target is now 1.66, it appears D4 went all the way to 50%, I'm okay with the figure being below 50%, 50% is simply the max the figure can go to. (Also, just to mention, the minimum is also 50% but can be in between as well)

The result in D4 should be 3600, only a 20% increase to achieve the target of 1.6

Don't understand what you trying to do. D1 is not the ending stock.
It's the initial stock used to calculate the ending stock (D7=D1+D4-D6) that is used to calculate the rate D7/D5.

For me, D1 is always equal to B1 (initial stock) and only changes when B1 changes.

M.

 

[saltwater]

Hello Marcelo Branco,

I am sorry for the delay in my response.

After a thorough review, it has been determined that the formula in D4 should calculate based on the Ending Stock in B7.
If I use the formula you provided, =IF(B9>1.5,MAX(D5*1.6+D6-D1,B4*(1-0.5)),B4*(1+0.5)), and I also update the formula in D7 to, =B7+D4-D6, and if the Purchase Order in B4 is 3000, the result in D9 is 1.66. The formula in D4 has calculated to the max of 50%. The answer should be 3600 in D4 for an increase of only 20%.

Do you have any suggestions?