Calculate value based on range
Hello Community!
*Caveat: I know enough about formulas to be dangerous but by no means understand nesting and such*
I would COL A (Points) to return a value based on a price range available in COL B (Price). For example:
$0.01 to $2.00= 1pt
$2.01 to $4.00= 2pts
$4.01 to $6.00 = 3pts
And so on
This is as far as I got: =IF(Price@row <= 2, 1, IF(Price@row <= 2.01, 2), IF(Price@row >=4.01, 3)))
which, for our experts, will quickly see is wrong.
Thank you!
Best Answer

=IF(Price@row <= 2, 1, IF(Price@row <= 4, 2, IF(Price@row >=4.01, 3)))
You were pretty close. give that a try and see if it works how you want it to.
Answers

=IF(Price@row <= 2, 1, IF(Price@row <= 4, 2, IF(Price@row >=4.01, 3)))
You were pretty close. give that a try and see if it works how you want it to.

P.S.
It was only an extra closing parenthesis after your second IF...

Thank you! I was able to use that and apply the logic to continue the range. Much appreciated!

@Paul Newcome while that was the only syntax error, the second if statement had a logic error in that it would have only caught values between 2.0 and 2.1

Help Article Resources
Categories
Check out the Formula Handbook template!