Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Vlookup for intervals
Don't think I was clear on my last question. Here is the table:
Bonus 0%-22.5% 7200 22.6%-23.8% 6600 23.9%-25% 6000 25.1%-26.3% 4800 26.4%-27.5% 3600 As you can see it is a range, not just one number. If our % is 26.9 what function do I use to calculate the bonus payoff? I know the answer is 3600, but how do I get excel to calculate it? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Vlookup for intervals
Set table in columns A & B as below:
0% 7200 22.60% 6600 23.90% 6000 25.10% 4800 26.40% 3600 to get your value for % in C1: =VLOOKUP(C1,$A$1:$A$5,2) HTH "UnisourceforNPH" wrote: Don't think I was clear on my last question. Here is the table: Bonus 0%-22.5% 7200 22.6%-23.8% 6600 23.9%-25% 6000 25.1%-26.3% 4800 26.4%-27.5% 3600 As you can see it is a range, not just one number. If our % is 26.9 what function do I use to calculate the bonus payoff? I know the answer is 3600, but how do I get excel to calculate it? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Vlookup for intervals
See my response to your other post. If you're entering the lookup_value of
26.9 as a PERCENTAGE, 26.9%, then you also need to format the left hand column of your table as PERCENTAGE. Use the *LOWER* boundary of each interval in your table: 0............7200 22.6.......6600 23.9.......6000 25.1.......4800 26.4.......3600 Any lookup_value = 26.4 will return 3600. Biff "UnisourceforNPH" wrote in message ... Don't think I was clear on my last question. Here is the table: Bonus 0%-22.5% 7200 22.6%-23.8% 6600 23.9%-25% 6000 25.1%-26.3% 4800 26.4%-27.5% 3600 As you can see it is a range, not just one number. If our % is 26.9 what function do I use to calculate the bonus payoff? I know the answer is 3600, but how do I get excel to calculate it? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Vlookup for intervals
Try this:
=LOOKUP(A1,{0,0.226,0.239,0.251,0.264},{7200,6600, 6000,4800,3600}) "UnisourceforNPH" wrote: Don't think I was clear on my last question. Here is the table: Bonus 0%-22.5% 7200 22.6%-23.8% 6600 23.9%-25% 6000 25.1%-26.3% 4800 26.4%-27.5% 3600 As you can see it is a range, not just one number. If our % is 26.9 what function do I use to calculate the bonus payoff? I know the answer is 3600, but how do I get excel to calculate it? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
autofill @ intervals | Excel Worksheet Functions | |||
Confidence Intervals | Excel Discussion (Misc queries) | |||
Dates and Intervals | Excel Worksheet Functions | |||
date intervals | Excel Worksheet Functions | |||
Time Intervals | Excel Worksheet Functions |