Between Two Numbers...
 

I need to have Excel calculate percentages of a cell based on where it falls
between numbers...for example:


Percentage of this number: (B2) $3,500.00

0 - 2500 = 25% $0.00 (B4)
2501 - 3500 = 30% $0.00 (B5)
3501 + = 35% $0.00 (B6)

So if B2 were $4000.00 the calculations should equal out to as follows:
2500 * 25% = 625
1000 * 30% = 300
500 x 35% = 175

Can this be done? I tried SUMIF, IF(AND), and stuff like that but nothing
seems to come out right.

Please help!

Between Two Numbers...
 

On Thu, 7 Feb 2008 12:47:04 -0800, GoodTrouble
wrote:

I need to have Excel calculate percentages of a cell based on where it falls
between numbers...for example:


Percentage of this number: (B2) $3,500.00

0 - 2500 = 25% $0.00 (B4)
2501 - 3500 = 30% $0.00 (B5)
3501 + = 35% $0.00 (B6)

So if B2 were $4000.00 the calculations should equal out to as follows:
2500 * 25% = 625
1000 * 30% = 300
500 x 35% = 175

Can this be done? I tried SUMIF, IF(AND), and stuff like that but nothing
seems to come out right.

Please help!

Set up a table and NAME it tbl

0 0 25%
2500 625 30%
3500 925 35%

If tbl refers to: H1:J3 then

I2: =I1+(H2-H1)*J1

Then, use this:

=(B2-VLOOKUP(B2,tbl,1))*VLOOKUP(B2,tbl,3)+VLOOKUP(B2,tb l,2)



--ron