if/and...not quite sure

  • Thread starter Thread starter Andmor
  • Start date Start date
A

Andmor

In cell D5 I will be entering in a figure, in cell G5 I need to multiply it
by a percentage, the percentage changes depending on the value of cell D5. I
have 8 ranges to accomadate the 1st one being a less than and the last one
being a more than.

Is this enough information to get some assistance?
 
In cell D5 I will be entering in a figure, in cell G5 I need to multiply it
by a percentage, the percentage changes depending on the value of cell D5. I
have 8 ranges to accomadate the 1st one being a less than and the last one
being a more than.

Is this enough information to get some assistance?
Click to expand...

Try this formula in cell G5:

=D5*VLOOKUP(D5,{-99999,1;10,2;20,3;30,4;40,5;50,6;60,7;70,8},2)/100

-99999 should just be a number less than then smallest possible value
in D5.

10,20,30,40,50,60,and 70 are the limit for the ranges.
Change them to suit your needs.

1,2,3,4,5,6,7, and 8 are the respective percentages.
Change them to suit your needs.

Example: If D5 is 34 the percentage is 4 and the result in cell G5
will be 1.36 (which is 4% of 34).

Hope this helps / Lars-Åke
 
I think this is on the right track, the values that come out at the end are
simply too high. Here are the figures I am working with.

first $37,106 20.05%
over $37,106 up to $40,970 24.15%
over $40,970 up to $65,345 31.15%
over $65,345 up to $74,214 32.98%
over $74,214 up to $76,986 35.39%
over $76,986 up to $81,941 39.41%
over $81,941 up to $127,021 43.41%
over $127,021 46.41%
 
If you mean that 20.05% should be applied to the first $37,106 and
24.15% to the part between $37,106 and $40,970 and so on, you can try
this:

Put a table in cells A1:C10 with the following content

A1: 0
A2 : 37106
A3 : 40970
and so on
A8: 127021

B1: 20.05%
B2: 24.15%
and so on
B8: 46.41%

C1: 0
C2: =(A2-A1)*B1+C1
C3: =(A3-A2)*B2+C2
and so on
C8: =(A8-A7)*B7+C7

If you input is in cell A10, put the following formula in the cell
where you want your result:

=(A10-VLOOKUP(A10,A1:A8,1))*VLOOKUP(A10,A1:B8,2)+VLOOKUP(A10,A1:C8,3)

Hope this helps / Lars-Åke
 
Back
Top