Putting together a logical check with combined conditions

D

DWS

New member
Joined
Apr 13, 2014
Messages
4
Reaction score
0
Points
0
Hello,

I'm not really experienced with excel formulas, and I was hoping someone help find out where I'm going wrong. What I am trying to do is if the three cells C4, H4 and I4, have met the conditions, I would like to apply a value into a cell new cell depending on the value in L28. I have a long list and the value in L28 can differ in value between 1 to over 365.

=IF(OR($C4="Inactive", $H4="NO",I4="YES"),0,IF(OR($L28<90,$M$6),IF(OR(L28>90,L28<180),$O$6),IF(OR(L28>181,L28<365),$O$6),IF(L28>365,$P$6))
 
Try:

=IF(OR($C4="Inactive",$H4="NO",I4="YES"),0,IF($L28<90,$M$6,IF(L28<180,$O$6,IF(L28<365,$O$6,$P$6))))
 
Thank you for the feed back. With my formula I would like to have four options. If the Number in L28, 90 and under, between 91 and 18, been 181and 365, and 365and over. Is there anyway to add this in your formula?
 
Those options are in my formula. Please test it with input variations to see how/if it works.
 
Hello,

In the formula above you have options for under 90,under 180 and under 365. I'm looking of an option for (90 and below, 91 to180, 181 to 365 and over 365). Using the formula I only have three options.
 
Ok, just change the < to <=

e.g

=IF(OR($C4="Inactive",$H4="NO",I4="YES"),0,IF($L28<=90,$M$6,IF(L28<=180,$O$6,IF(L28<=365,$O$6,$P$6))))

Note: The IF function works on a "stop" when TRUE rule.

So after it's checked for the C4,H4,I4 checks and resulted that all three are FALSE, then the L28 is checked. It first checks if L28 is less than or equal to 90, if yes, it stops, if no, it checks next if it is less than 180 (it doesn't have to check if it is higher than 90 as it already checked if it was lest than 90, so by default it would be higher than 90. IF TRUE it stops, if not, then it goes to check if it is less than or equal to 365 and uses the same logic as before. Finally if it is not less than 365, it is assumed to be greater, so P6 will return because that is the what if False part.
 
Last edited:
Okay now I understand, it worked great thank you for all your help!!!
 
NBVC said:
Try:

=IF(OR($C4="Inactive",$H4="NO",I4="YES"),0,IF($L28<90,$M$6,IF(L28<180,$O$6,IF(L28<365,$O$6,$P$6))))
Click to expand...

my friend so I can check where the input variable and add your examples on how I can guide with pictures please? thank you very much
 
In your spreadsheet enter anything into cells M6, O6, and P6.

Put the formula anywhere else, say in Q6. Now put a number in L28. One of the items from M, O or P should now be in the formula cell. Keep changing the number with variations of numbers between 0 and say 500, you should see different results, accordingly.

After seeing the formula again, I think the OP meant to use N6 for <180. It would make better sense.

e.g.


=IF(OR($C4="Inactive",$H4="NO",I4="YES"),0,IF($L28<90,$M$6,IF(L28<180,$N$6,IF(L28<365,$O$6,$P$6))))
 
You must log in or register to reply here.
Back
Top