I think it's an IF formula.... but I can't get it to work

mickyd67

New Member
Joined
Jul 13, 2011
Messages
25
Hi all - really hoping an Excel genius can work this one out for me :cool:

Scenario is:

I want to review the safety stock levels of a number of components. I've taken 2 components as an example

If the percentage adherence to the agreed safety level, over a period of time, falls below a certain threshold it should return 'Yes' (the cells that drop below this threshold have a conditional format to highlight where they are below this percentage)

If the level doesn't fall below the threshold then it should return 'No'. However, it should also return 'No' if there is no safety stock level set in the first place - this is where I am getting stuck.

I want the formula to first look to see if there is a safety level set - then tell me if, at any point, it drops below the threshold during the 13 week period.

I want the 'Yes' or 'No' to display in column G, along from the associated component code.

Hope that makes sense :-/




 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,338
Office Version
2019, 2016, 2013
Platform
Windows
what is your current formula
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
766
Office Version
365
Platform
Windows
In G9 try this:
=IF(AND(ISNUMBER($E$4),MIN(J9:V9)<$E$4),"Yes","No")
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,412
Office Version
365
Platform
Windows
It would also be helpful to know the structure of your sheet, i.e. what cell the "safety set level" exists in, and where the 13 weeks of data exists.
And, is the "safety set level" also a percentage like the 13 weeks of data?
 

mickyd67

New Member
Joined
Jul 13, 2011
Messages
25
what is your current formula
Hi - thanks for the quick reply:

This is what I am currently using - probably not the best way to do it, but it's the only way I've found so far:

=IF(OR(J9<$E$4,K9<$E$4,L9<$E$4,M9<$E$4,N9<$E$4,O9<$E$4,P9<$E$4,Q9<$E$4,R9<$E$4,S9<$E$4,T9<$E$4,U9<$E$4,V9<$E$4),"Yes","No")

That works if there's a level set, so works for the first component code

However, because the second component doesn't have a level set the results are all 0% and it returns a 'Yes' answer


In G9 try this:
=IF(AND(ISNUMBER($E$4),MIN(J9:V9)<$E$4),"Yes","No")
Thank you - let me try that.

It would also be helpful to know the structure of your sheet, i.e. what cell the "safety set level" exists in, and where the 13 weeks of data exists.
And, is the "safety set level" also a percentage like the 13 weeks of data?
Noted - let me try the above and if that doesn't work I'll try and explain more on how the sheet is laid out.

Thanks again to you all for the quick replies :)
 

mickyd67

New Member
Joined
Jul 13, 2011
Messages
25
In G9 try this:
=IF(AND(ISNUMBER($E$4),MIN(J9:V9)<$E$4),"Yes","No")
That works for the first component, as it has a safety level set

However it doesn't work for the 2nd, as there is no level set and the weekly results are all at 0%

Theoretically 0% could be a result - so I need the formula to first check if there is a safety level set, and return No if there's not.

Hope that makes sense :-/
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
766
Office Version
365
Platform
Windows
I assumed cell E4 was a generic safety level for all components, where can the safety levels be found otherwise for other components?

If a safety level is set for each component (a column designed for this would be ideal) then the formula can reference the safety level and be dragged down.
 

mickyd67

New Member
Joined
Jul 13, 2011
Messages
25
Hi All - I am still battling with this one. I thought I had it working, but alas....

I've tried to simplify what I am asking.

If an item has no 'Agreed Safety Quantity' then the result in the yellow box should always return No

If an item has an 'Agreed Safety Quantity' but doesn't drop below the tolerance level in Cell D10 it should return No

If there in an agreed quantity and that drops below the safety level tolerance then it should return Yes

Really hope someone can help - banging my head up against a wall....

 

mickyd67

New Member
Joined
Jul 13, 2011
Messages
25
For reference - the formula I have, partially, working is:

Code:
=IF(COUNTIF(C13:G13,"<"&$D$10),"Yes","No")
However if I use that for Item 1, on Row 13, it returns Yes - because 0% is below the tolerance. However, as I say above, it should be No as there's no safety quantity in the first place.

The formula works for Row 14 & 15 as there is a level set.
 
Last edited:

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
766
Office Version
365
Platform
Windows
Change your formula with this:

=IF(AND(COUNTIF(C13:G13,"<"&$D$10),B13<>0),"Yes","No")

Now you're telling the formula to have both criteria to flag yes - i.e. at least 1 value falls below the level and that the safety quantity is not equal to 0 - otherwise "no" is returned.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,073
Messages
5,466,447
Members
406,482
Latest member
RLPeloquin

This Week's Hot Topics

Top