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

[mickyd67]

Hi all - really hoping an Excel genius can work this one out for me

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 :-/

 

[mole999]

what is your current formula

 

[tyija1995]

In G9 try this:
=IF(AND(ISNUMBER($E$4),MIN(J9:V9)<$E$4),"Yes","No")

 

[Joe4]

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]

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]

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]

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]

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]

For reference - the formula I have, partially, working is:

=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.

 

[tyija1995]

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.