Rounding Logic Question

[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.

 

[licksy]

Sorry, forgot to mention Excel version 200

 

[Niek Otten]

Hi Mark,

Always be very prcise as to what you want the borders to be.
In your case, the second column seems to be superfluous.
With the value to be looked up in D2, and your table in A1:C6, use this
formula:

=VLOOKUP(D2,$A$2:$C$6,3)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Mike B]

Mark

'One option is a user defined function to use as a formula
'For example in the function below
'price is the value in col D
'rounded_range is the range in col C
'place the function in col E, make the rounded range absolute $
'and copy down 150 rows

Function Round_Value(price, Rounded_Range)
Application.Volatile
Round_Value = "error"
For Each cell In Rounded_Range 'all data cells col C

Select Case cell.Offset(0, -2) 'compare col A
Case Is <= price
If cell.Offset(0, -1) >= price Then 'compare col B
Round_Value = cell.Value
Exit Function
End If
End Select
Next
End Function

HTH

Mike B