Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding Logic Question
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. --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding Logic Question
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding Logic Question
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 "licksy " wrote in message ... 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. --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding Logic Question
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Logic question | Excel Worksheet Functions | |||
Check box logic question | Excel Worksheet Functions | |||
IF(?) logic question | Excel Discussion (Misc queries) | |||
Logic question | Excel Discussion (Misc queries) | |||
Logic Question - Can you help please | Excel Programming |