• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Greater than less than and between

Aspur

New Member
Hello all,

I'm trying to make one formula to assign, A, B or C depending on the dollar value in a cell. I can write the formulas separately but putting them together I'm really confused

A:> $516,800: 80%
B: $183,116 – $516,800 : 81%-95%
C: < $183,116: 96% - 100%

=IF(B2:B2>516800,"A")
=IF(AND(B2>183116,B2<516800),"B","C")

How can I consolidate these two formulas to one
 
I think I just answered my own question .. Please correct me if I'm wrong. It seems to be working.


=IF(B2>=516800, "A", IF(B2>=183116, "B", IF(B2<516800, "C")))
 
Your formula :
=IF(B1>=516800,"A",IF(B1>=183116,"B",IF(B1<516800,"C")))

It is shorter and same as the above :
=IF(B1>=516800,"A",IF(B1>=183116,"B","C"))

or,
=IF(B1<183116,"C",IF(B1<516800,"B","A"))

Regards
Bosco
 
Last edited:
Just to offer a radically different approach:
= LOOKUP( percentage, {0,"A";0.8,"B";0.95,"C"} )
will test the percentages rather than the dollar amounts.

Note: If the threshold percentages ever change they should be in a table rather than the embedded constant
 
Just to offer a radically different approach:
= LOOKUP( percentage, {0,"A";0.8,"B";0.95,"C"} )
will test the percentages rather than the dollar amounts.

Note: If the threshold percentages ever change they should be in a table rather than the embedded constant


Thanks so much. I'll keep this one in my back pocket. The % will change but not until the next fiscal period.
 
Your formula :
=IF(B1>=516800,"A",IF(B1>=183116,"B",IF(B1<516800,"C")))

It is shorter and same as the above :
=IF(B1>=516800,"A",IF(B1>=183116,"B","C"))

or,
=IF(B1<183116,"C",IF(B1<516800,"B","A"))

Regards
Bosco


Thank you so much. I always get great replies from this forum. Great talented folks!
 
Back
Top