Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup rounding up rather than down
I'm trying to make a chart of State mandated insurance rates. One
column is policy amounts in $500 increments: 10,000, 10,500, 11,000 etc. Another column is rates for each policy amount: 229, 233, 235 (no set pattern), I set it in Vlookup; VLOOKUP(PolicyAmt,Rate, 2,TRUE). I said TRUE because I need the amount for everything in between the charted increments, e.g, 10,200.55. It doesn't work. The rates are _up to and including_ the set amounts, so 10,200.55 (or whatever) coverage should have a 233 premium. Vlookup returns the lower amount, so 10,250.55 coverage shows just 229. Excel Help says Vlookup looks for the exact value in the left column or the next _lower_ value. Is there any way to round up instead of down? Am I trying to make water flow uphill? Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup rounding up rather than down
Try table as:
99999 240 <=== your maximum 11000 235 10500 233 10000 229 and use formula: =INDEX(B1:B4,MATCH(D1,A1:A4,-1)) where D1 is your lookup value HTH "Steve" wrote: I'm trying to make a chart of State mandated insurance rates. One column is policy amounts in $500 increments: 10,000, 10,500, 11,000 etc. Another column is rates for each policy amount: 229, 233, 235 (no set pattern), I set it in Vlookup; VLOOKUP(PolicyAmt,Rate, 2,TRUE). I said TRUE because I need the amount for everything in between the charted increments, e.g, 10,200.55. It doesn't work. The rates are _up to and including_ the set amounts, so 10,200.55 (or whatever) coverage should have a 233 premium. Vlookup returns the lower amount, so 10,250.55 coverage shows just 229. Excel Help says Vlookup looks for the exact value in the left column or the next _lower_ value. Is there any way to round up instead of down? Am I trying to make water flow uphill? Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup rounding up rather than down
On 15 Mar 2007 12:41:22 -0700, "Steve" wrote:
I'm trying to make a chart of State mandated insurance rates. One column is policy amounts in $500 increments: 10,000, 10,500, 11,000 etc. Another column is rates for each policy amount: 229, 233, 235 (no set pattern), I set it in Vlookup; VLOOKUP(PolicyAmt,Rate, 2,TRUE). I said TRUE because I need the amount for everything in between the charted increments, e.g, 10,200.55. It doesn't work. The rates are _up to and including_ the set amounts, so 10,200.55 (or whatever) coverage should have a 233 premium. Vlookup returns the lower amount, so 10,250.55 coverage shows just 229. Excel Help says Vlookup looks for the exact value in the left column or the next _lower_ value. Is there any way to round up instead of down? Am I trying to make water flow uphill? Thanks. Offset your premiums by one row, so they "line up" in accord with the VLOOKUP requirements. =VLOOKUP(10200.55,{9500,229;10000,233;10500,235;11 000,242},2) You may have to change the values a bit: =VLOOKUP(10200.55,{9499.99,229;9999.99,233;10499.9 9,235;10999.99,242},2) --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup rounding up rather than down
On Mar 15, 3:29 pm, Ron Rosenfeld wrote:
Thanks for the input. Maybe I didn't fully set out the issue. The State sets rates from zero to 100,000. Premiums are 229 up to and including 10,000; 233 up to and including 10,500, and so on to 833 for up to and including 100,000. This means I have 180 entries in the column of policy amounts and the same number of premium amounts. I tried to set up the formula for policy amounts exactly matching the 180 breaks set by the state and everything else in between. If my formula had to deal with multiples of 500 and everything else, there would be ten million iterations. That's what I'm trying to avoid. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup rounding up rather than down
On 15 Mar 2007 16:04:11 -0700, "Steve" wrote:
On Mar 15, 3:29 pm, Ron Rosenfeld wrote: Thanks for the input. Maybe I didn't fully set out the issue. The State sets rates from zero to 100,000. Premiums are 229 up to and including 10,000; 233 up to and including 10,500, and so on to 833 for up to and including 100,000. This means I have 180 entries in the column of policy amounts and the same number of premium amounts. I tried to set up the formula for policy amounts exactly matching the 180 breaks set by the state and everything else in between. If my formula had to deal with multiples of 500 and everything else, there would be ten million iterations. That's what I'm trying to avoid. You either didn't try or didn't understand my response. If the first "bracket" is from 0-10000, then merely set the first entry to 0 instead of the 9500. Since you wrote that the premiums did not occur in any particular pattern, you will have to have a separate entry in your table for each bracket. I don't know of any way around that if there is no mathematical relationship between the amount and the premium. So you still just need to offset your table as I wrote. For example: $ 0.00 229 $10,000.01 233 $10,500.01 235 =VLOOKUP(amt,tbl,2) where amt is the amount you are checking; tbl is the two column table set up as above and extended through to $100,000 --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rounding off to .48 or .98 | Excel Discussion (Misc queries) | |||
rounding | New Users to Excel | |||
Vlookup Rounding | Excel Discussion (Misc queries) | |||
Rounding criteria within a nested vlookup and hlookup | Excel Worksheet Functions | |||
Rounding up and down | Excel Worksheet Functions |