2

Ok, I'm going to try to be as specific as possible, but I'm racking my brain over an excel formula I can't figure out. What I'm trying to accomplish is to have one of three things populate based on a few variables in other cells.

Basically, what I want it to do is :

If L1 is > 0 and J1, N1, and P1 is 0, then populate the Cell with "silver".

If J1, N1, or P1 is more than 0, populate with "low".

Unless V1 is less than 50, in which case populate with "High".

I have gotten close a few times, but ultimately I can't figure it out and have been at it for several hours. Any help would be greatly appreciated!

Improve this question
2
  • 2
    so if v1 is less than 50 no matter what is in L1,j1,n1 and p1 it should be "High"?
    – Scott Craner
    Commented Jul 22, 2021 at 21:48
  • 1
    also what if L1 <= 0 what happens then?
    – Scott Craner
    Commented Jul 22, 2021 at 21:48

3 Answers 3

Reset to default
1

You'll want to structure your IF formula based on which scenarios take priority.

=IF([first scenario], [first scenario value], IF([second scenario], [second scenario value], IF([third scenario], [third scenario value])))

In your case, it would be the following:

=IF(AND(L1>0, J1=0, N1=0, P1=0), "Silver", IF(V1<50, "High", IF(OR(J1>0, N1>0, P1>0), "Low")))
Improve this answer
1
  • Had to flip a couple of things, but looks like it's working perfectly now! Thanks again for the help! I have to figure out a few more weird ones and if I have any questions I'll give you a shout!
    – Chris Hampton
    Commented Jul 22, 2021 at 23:59
0

This is my best guess based on what is in the question.

Use a nested IF:

=IF(V1<50,"High",IF(L1>0,IF(AND(J1=0,N1=0,P1=0),"Silver","Low"),"")
Improve this answer
13
  • I'm pretty sure that did it! I think some of my percentages are off which is giving me wonky results, but it's working! Thanks so much, Scott! COKE ZERO HEADING YOUR WAY!
    – Chris Hampton
    Commented Jul 22, 2021 at 22:43
  • Ok, so I was wrong. It is acting wonky. I had to swap a couple of things, but its working well kinda. Here is what i have now. =IF(V2>80,"Low Grade",IF(L2>0,IF(AND(J2=0,N2=0,P2=0),"Silver","Karat Scrap"),"")) The problem im having now is that some that should be marked "silver" are still being marked "low grade". I am trying to make it so that regardless of the value of V2, " silver" is populated if J, N, and P are 0.
    – Chris Hampton
    Commented Jul 22, 2021 at 22:51
  • ok, so im back now that i have all of my data entered and im trying to get all of this working together. I still have an issue. As of now, my formula is working properly, however i need any cells that arent calculating due to missing info to be blank instead of showing an error because its messing up other formulas. This is the formula that im currently using that works properly. =IF(AND(L2>0, J2=0, N2=0, P2=0), "Silver Bearing", IF(W2>40, "Low Grade", IF(OR(J2>0, N2>0, P2>0), "Karat Scrap")))
    – Chris Hampton
    Commented Aug 3, 2021 at 17:37
  • If it is just dealing with errors wrap the formula in =IFERROR(...,"") where ... is the formula you are using.
    – Scott Craner
    Commented Aug 3, 2021 at 17:39
  • 1
    Thanks. We can consider this closed and ill start a new question if i cant figure it out. I appreciate all of your help.
    – Chris Hampton
    Commented Aug 3, 2021 at 17:57
0

What would you want to get if L1 <= 0, but at the same time V1 >= 50? Or if J1, N1, and P1 all <=0, at the same time V1 >= 50?

If you allow the results of scenarios above are blank, you may try the formula `

=IF(V1<50,"High",IF(OR(J1>0,N1>0,P1>0),"Low",IF(AND(L1>0,J1=0,N1=0,P1=0),"Silver",""))).

enter image description here

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .