IF statement (with threshold?)

saltwater

New Member
Joined
Feb 19, 2014
Messages
29
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,317
Could you provide some examples?
Something like
Target = 1.6
B9 = 1; B4 = 2; Desired result = ?
B9 = 2; B4 = 2; Desired result = ?
etc

M.
 

BlakeSkate

Well-known Member
Joined
Jan 26, 2015
Messages
618
Office Version
2016
Platform
Windows
(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.

FormulaTargetThreshold
?1.6.4
?1.6.5

<tbody>
</tbody>

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

New Member
Joined
Feb 19, 2014
Messages
29

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!

Stock16000Stock16000
Purchase Order8000Adjusted Qty.4000
Average14000Average14000
Outgoing Orders100Outgoing Orders100
Ending Stock23900Ending Stock19900
Target1.71Adjusted Target1.42

<tbody>
</tbody>
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,317
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.
 
Last edited:

saltwater

New Member
Joined
Feb 19, 2014
Messages
29
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,317
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.
 
Last edited:

saltwater

New Member
Joined
Feb 19, 2014
Messages
29
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
 
Last edited:

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,317
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

New Member
Joined
Feb 19, 2014
Messages
29
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?
 

Forum statistics

Threads
1,082,587
Messages
5,366,486
Members
400,894
Latest member
frog9000

Some videos you may like

This Week's Hot Topics

Top