CODEWITHSUNDEEP
excelvba
PipS
PipS

Reputation: 5

Greater than, less than - multiple solutions

I need to search Column V (called TotalWeight).

If it's under 100 then in Column W it will say "L"
between 101 - 250 "H"
between 250 - 500 "VH"
Over 500 "VVH"

I tried

Sub Change_Weight()

    =IF([TotalWeight]0 > 100, “L”, IF([TotalWeight]101 > 250, “H”, IF([TotalWeight]250 > 500, “VH”, IF([TotalWeight]500 > 900, “VVH”))))

End Sub

Upvotes: 0

Views: 67

Answers (2)

Ike
Ike

Reputation: 13054

What I would suggest, is to put your conditions into a separate table. Any time your boss wants different values or wants more conditions it is much easier to maintain that list than to change the formula.

Put this formula into Column V:

=INDEX(configTotalWeight[Description],MATCH(A3,configTotalWeight[TotalWeight],1))

https://i.imgur.com/yRmXvsJ.png

Upvotes: 0

James
James

Reputation: 68

It might be better to work backwards from the heaviest weight to the lightest, with the lightest being the last 'False' result, so:

=If([TotalWeight]>500,"VVH",IF([TotalWeight]>250,"VH",IF([TotalWeight]>100,"H","L")))

Upvotes: 1

Related Questions