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

mickyd67

New Member
Joined
Jul 13, 2011
Messages
11
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 :-/




 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
648
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
51,414
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
11
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
11
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
648
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
11
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
11
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
648
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.
 

Forum statistics

Threads
1,081,480
Messages
5,358,946
Members
400,515
Latest member
Finagill

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top