How to write If Formula and Divide

[WarrenCarr]

I need to write a formula that will do this:

IF CELL1is <3000 AND CELL2 is <250 display "SMB"

**its this second part that I cant really figure out all to well**

IF (CELL1 is >=3000 but <15000) Divided by 4 is greater than CELL2 which is between 250 - 3000 is TRUE display "Multi-Family", FALSE display "Commercial

** So to use words I want a formula. There is a 4:1 ratio between CELL1 and Cell 2. So if CELL1 is greater than CELL2 when divided by 4 display xyz or abc depending on true or false**

IF CELL1 is >=15000 OR CELL2 is >=3000 than display "Strategic Sales"

I hope someone can help me. Hopefully I made it clear what I want. I sorta got parts of it figured out but I really cant figure out the division portion. Any help is greatly welcomed.
Thanks

 

[Jeffrey Mahoney]

To each of your needs for formulas

=IF(AND(A1<3000,B1<250),"SMB","Nope")

=IF(AND(E1>=3000,E1<15000,E1/4>F1),"Multi-Family","Commercial")

=IF(OR(H1>=15000,I1>=3000),"Strategic Sales","Nope")

 

[Jeffrey Mahoney]

The AND function and the OR function return a TRUE or FALSE. The IF function needs a TRUE or FALSE as a result of your test, and then returns the appropriate given response.

Jeff

 

[WarrenCarr]

Great that looks like it should work! I'll give it a try. Thanks so much! I am kinda out of my league with Excel!

 

[WarrenCarr]

So what would it look like in one line of formula? I can't figure out how to embed it properly.

 

[Jeffrey Mahoney]

If(TEST=TRUE, do this, otherwise do that)

Nested IF statements can be built using that same format over and over. Red is the Test, Blue is the TRUE result. Green is the FALSE result

=IF(AND(A1<3000,B1<250),"SMB",IF(OR(A1>=15000,B1>=3000),"Strategic Sales",IF(AND(A1>=3000,A1<15000,A1/4>B1),"Multi-Family","Commercial")))

Jeff

 

[WarrenCarr]

=IF(AND(B2<3000,B3<250),"SMB",IF(OR(B2>=15000,B3>=3000),"Strategic Sales",IF(AND(B3>=250,B3<3000,B2>=3000,B2<15000,B2/4>B3),"Multi-Family","Commercial")))

So this is what I have so far. I made some minor modifications to the formula that you gave. But now I am running into the problem of that it will only display "Multi Family" if the commercial side is above 250. Which isn't quite what I want. I tried changing the AND to OR but that doesn't quite solve the problem. It fixes this issue but creates another.

I want to display "SMB" when B2<3000 AND B3<250
I want to display "Strategic" when B2>=15000 OR B3>=3000
I want to display "Multi Family" when B2>3000,<15000 OR if it B3>B2/4
I want to display "Commercial when B3 >250,<3000

I feel like it's so close.
Thanks so much for all the help I really don't know much about excel. You've been super helpful.

 

[Jeffrey Mahoney]

We still may need some small modifications. The Multi-Family and the Commercial logic was changed. Let's see of that's the ticket. I put in Nope at the end so that you could see if all the logic works. Hopefully the logic captures all the possibilities.

=IF(AND(B2<3000,B3<250),"SMB",IF(OR(B2>=15000,B3>=3000),"Strategic Sales",IF(AND(B3>=250,B3<3000,B2>3000,B2<15000,B3>(B2/4)),"Multi-Family",IF(AND(B3>250,B3<3000),"Commercial","Nope"))))

 

[Jeffrey Mahoney]

Do you know how to evaluate formulas. There are some tricks that help you get results from pieces of the formula.

While you are in edit mode you can highlight parts of the formula and press F9. It turns the formula into a value so you can see the result for just that part. Be careful; because this part is now a value, if you hit enter, that part of the formula will be gone; so it is best to hit escape after doing this evaluation. The other part of this is that Excel will only give you a result if you select the right blocks. In the example below, all the red text are examples of blocks you can select. Excel will warn you if the block you select won't give you a result.

=IF(AND(B2<3000,B3<250),"SMB",IF(OR(B2>=15000,B3>=3000),"Strategic Sales",IF(AND(B3>=250,B3<3000,B2>3000,B2<15000,B3>(B2/4)),"Multi-Family",IF(AND(B3>250,B3<3000),"Commercial","Nope"))))

 

[WarrenCarr]

Ok I think its working now. I made some few adjustments from the code you gave and it seems to be all set.

=IF(AND(B2<3000,B3<250),"SMB",IF(OR(B2>=15000,B3>=3000),"Strategic Sales",IF(AND(B2>=3000,B2<15000,B3<(B2/4)),"Multi-Family",IF(AND(B3>=250,B3<3000),"Commercial","Nope"))))

Thanks for all the help. I couldn't have done it without you!