Help using Tables

  • Thread starter Thread starter Jody
  • Start date Start date
J

Jody

Hi guys!





I need some help. I have a table that has a percentage range. Like this.


Less than 90%
0

90.0%
90.9%
$ 2,625

91.0%
91.9%
$ 3,413

92.0%
92.9%
$ 4,200

93.0%
93.9%
$ 4,988

94.0%
94.9%
$ 5,775

95.0%
95.9%
$ 6,563

96.0%
96.9%
$ 7,350

97.0%
97.9%
$ 8,138

98.0%
98.9%
$ 8,925

99.0%
99.9%
$ 9,713

100.0%
100.9%
$ 10,500

101.0%
101.9%
$ 12,600

102.0%
102.9%
$ 14,700

103.0%
103.9%
$ 16,800

104.0%
104.9%
$ 18,900

105.0%
105.9%
$ 21,000


Over 105.9 %
$700 per 1%




What I need to do is be able to input a percent in a cell, then by the table
above, report the number in column C. So if I input 95.2% then I need the
cell to read $6,563. I usually use IF for these things, but I think there's
a better way to do it and I don't think IF can handle this many different
scenarios. I think you can use tables, but not sure how to do it. Any help
would be REALLY appreciated!
 
Here's the table you should set up:

0% 0
90% 2,625
91% 3,413
92% 4,200
93% 4,988
94% 5,755
95% 6,563
96% 7,350
97% 8,138
98% 8,925
99% 9,713
100% 10,500
101% 12,600
102% 14,700
103% 16,800
104% 18,900
105% 21,000
106% 700 per 1%


Let's assume the table is in cells A1 through B18. Let's also assume cell D1
is where you want to punch in any percentage and E1 is where you want the
result to appear.

In cell E1, use the following formula:

=IF(LEN(D1)>0,VLOOKUP(D1,$A$1:$B$18,2),"")

This formula checks the LENgth of cell D1. If its greater than zero than use
the VLOOKUP function to locate the closest match to D1 in the table that's
in $A$1:$B$18. The ",2" in the VLOOKUP indicates which column in the table
has the result you want to grab. If the LENgth is not greater than zero
(which means there's nothing in cell D1, display nothing (which is the set
of double quotes at the end of the function).

--
_________________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel
http://www.r-cor.com
 
Jody,

Have a loo at the VLOOKUP function.
Put the lower bounds of your percentages in a column ( 0% ; 90% ; 91% etc)
and the amounts in a second column.
Then enter in a cell your percentage (95,2% in you example; and in the cell
next to it the VLOOKUP function)

NB
1)What do you take - if possible with input of an percentage between (for
instance) 90,9% en 100%.
With the VLOOKUP setup as above, it end up in the same "class" as a
percentage between 90% and 90,9%.
If that is NOT what you want, you have to set the first column different (
0%; 90%; 90,9% etc.)

2) You will have to cope with percentages above 105,9% on a different way.

The total formula will become something like (lets say cell A1 contains
yyour input) : IF(A1>105,9%,(A1/0.01)*700,VlOOKUP(A1,LOOKUPRANGE,2,0))
(This assumes that the range of amount are taken up in the second column,
right to the percentages)

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Place the first percentage in cell A1 (0%) and the corresponding value (0)
in cell B1 - place the next percentage in cell A2 (90%) and its
corresponding value (2,625) in cell B2 - and so on.
_________________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel
http://www.r-cor.com
 
Back
Top