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

 

[mickyd67]

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.

Genius!!

Thanks very much for your help (and patience) with this one. You've made my life much easier with this.

 

[tyija1995]

No problems glad we got there in the end, have a good day!

 

[mickyd67]

Hi all - looking for help again. Similar theme to this issue that's already been solved. I thought I'd be able to work this out by adjusting the current formula but I'm struggling.

This time I want to count the number of weeks, over a 13 week period, that a percentage drops below the 75% tolerance level (this number could change so the formula needs to reference cell C5)

I can't do a countif because it would return 13 for the first item - and that should be 0 because there's no safety level set in the first place, if you get what I mean?





Here is the URL for above if it makes it a bit easier to see and, hopefully, removes that annoying watermark.

 

[tyija1995]

Hey again!

In the image linked above are you using cell F8 as the safety level, which is 0 and hence the count formula should also return 0?

Assuming your formula is going in to cell M8 first then try this:

=SUMPRODUCT((N8:Z8<$C$5)*(F8<>0))

 

[mickyd67]

Hey again!

In the image linked above are you using cell F8 as the safety level, which is 0 and hence the count formula should also return 0?

Assuming your formula is going in to cell M8 first then try this:

=SUMPRODUCT((N8:Z8<$C$5)*(F8<>0))

haha, Hello Again!!

This works perfectly - thanks again. Brilliant help

 

[tyija1995]

You're welcome, have a good day