L
licksy
Hello.
I am trying to work out a method for rounding prices to specific price
points.
Say for example I enter a price into cell D1, I want the cell E1 to
search through the range to find what the appropriate rounded value
should be.
Take the following 3 columns - if the price is between A and B, I want
the value from C.
I can do this using a messy formula nesting IF and AND functions for a
small range, something like
=IF(AND($D$1<=B1,$D$1>=A1),C1,IF(AND($D$1<=B2,$D$1>=A2),C2,"error"))
but I get stumped quickly because the range will have approx 150 rows.
A B C
1.08 1.34 1.29
1.35 1.55 1.49
1.56 1.80 1.79
1.81 2.07 1.99
2.08 2.34 2.29
etc.
I'd like this solution to be availabe for multiple workbooks but I'm
not familiar with macros very much.
Thanks and regards,
Mark.
I am trying to work out a method for rounding prices to specific price
points.
Say for example I enter a price into cell D1, I want the cell E1 to
search through the range to find what the appropriate rounded value
should be.
Take the following 3 columns - if the price is between A and B, I want
the value from C.
I can do this using a messy formula nesting IF and AND functions for a
small range, something like
=IF(AND($D$1<=B1,$D$1>=A1),C1,IF(AND($D$1<=B2,$D$1>=A2),C2,"error"))
but I get stumped quickly because the range will have approx 150 rows.
A B C
1.08 1.34 1.29
1.35 1.55 1.49
1.56 1.80 1.79
1.81 2.07 1.99
2.08 2.34 2.29
etc.
I'd like this solution to be availabe for multiple workbooks but I'm
not familiar with macros very much.
Thanks and regards,
Mark.