Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Lookup
I have to create a formula in say cell B25 to calculate a value called say
'multiplier' based on ratio (or percentage) of attainment with respect to goals. I have a cell say B23 which contains the value of this percentage in one cell of my worksheet and based on this value, I have to find a multiplier. I have the following table for finding the multiplier. eg. if the ratio (or percentage was 115% (value in B23) then this formula should be able to look up the multiplier in the follwing table; based on this table, it would fall in the 3rd row range and should result 1.1 From To Multiplier 0% 49% 0 50% 109% 1 110% 119% 1.1 120% 129% 1.2 130% 139% 1.3 140% 149% 1.4 150% 169% 1.5 170% 199% 1.6 200% 239% 1.7 240% + 2 How can I do this |
#2
|
|||
|
|||
On Mon, 12 Sep 2005 15:21:03 -0700, neeraj
wrote: I have to create a formula in say cell B25 to calculate a value called say 'multiplier' based on ratio (or percentage) of attainment with respect to goals. I have a cell say B23 which contains the value of this percentage in one cell of my worksheet and based on this value, I have to find a multiplier. I have the following table for finding the multiplier. eg. if the ratio (or percentage was 115% (value in B23) then this formula should be able to look up the multiplier in the follwing table; based on this table, it would fall in the 3rd row range and should result 1.1 From To Multiplier 0% 49% 0 50% 109% 1 110% 119% 1.1 120% 129% 1.2 130% 139% 1.3 140% 149% 1.4 150% 169% 1.5 170% 199% 1.6 200% 239% 1.7 240% + 2 How can I do this The table below is functionally equivalent - format the first column as a % 0.50 1 1.10 1.1 1.20 1.2 1.30 1.3 1.40 1.4 1.50 1.5 1.70 1.6 2.00 1.7 2.40 2 Give the table a range name - say "MyTable" Now in B25 enter the formula =vlookup(B23,MyTable,2) Rgds __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#3
|
|||
|
|||
One method without a table.
=LOOKUP(B23,{0,50,110,120,130,140,150,170,200,240} ,{0,1,1.1,1.2,1.3,1.4,1.5,1.6,1.7,2}) Gord Dibben Excel MVP On Mon, 12 Sep 2005 15:21:03 -0700, neeraj wrote: I have to create a formula in say cell B25 to calculate a value called say 'multiplier' based on ratio (or percentage) of attainment with respect to goals. I have a cell say B23 which contains the value of this percentage in one cell of my worksheet and based on this value, I have to find a multiplier. I have the following table for finding the multiplier. eg. if the ratio (or percentage was 115% (value in B23) then this formula should be able to look up the multiplier in the follwing table; based on this table, it would fall in the 3rd row range and should result 1.1 From To Multiplier 0% 49% 0 50% 109% 1 110% 119% 1.1 120% 129% 1.2 130% 139% 1.3 140% 149% 1.4 150% 169% 1.5 170% 199% 1.6 200% 239% 1.7 240% + 2 How can I do this |
#4
|
|||
|
|||
Thanks, it worked except the first row, if the value to be looked up is less
than 0.5, it gives a #N/A error (this is as expected because if lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error ). I had to add another row to your table: 0.0% 0 0.50 1 1.10 1.1 ...... "Richard Buttrey" wrote: On Mon, 12 Sep 2005 15:21:03 -0700, neeraj wrote: I have to create a formula in say cell B25 to calculate a value called say 'multiplier' based on ratio (or percentage) of attainment with respect to goals. I have a cell say B23 which contains the value of this percentage in one cell of my worksheet and based on this value, I have to find a multiplier. I have the following table for finding the multiplier. eg. if the ratio (or percentage was 115% (value in B23) then this formula should be able to look up the multiplier in the follwing table; based on this table, it would fall in the 3rd row range and should result 1.1 From To Multiplier 0% 49% 0 50% 109% 1 110% 119% 1.1 120% 129% 1.2 130% 139% 1.3 140% 149% 1.4 150% 169% 1.5 170% 199% 1.6 200% 239% 1.7 240% + 2 How can I do this The table below is functionally equivalent - format the first column as a % 0.50 1 1.10 1.1 1.20 1.2 1.30 1.3 1.40 1.4 1.50 1.5 1.70 1.6 2.00 1.7 2.40 2 Give the table a range name - say "MyTable" Now in B25 enter the formula =vlookup(B23,MyTable,2) Rgds __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |