Is this type of formula possible in Excel?
 

I am trying to apply a multiplication factor that would vary depending on the
sum of a particular cell. For example let's say the value of cell A5 is the
SUM of A1:A4. Let's say the value is 255,000,000.

my multiplication factor table looks like this:

1 - 50,000,000.........................(0.17)/1000
50,000,001 - 100,000,000..........(0.13)/1000
100,000,001 - 250,000,000.........(0.10)/1000
250,000,001 - 500,000,000.........(0.08)/1000
500,000,001 - 750,000,000.........(0.07)/1000
750,000,000 ++ .......................(0.05)/1000

So depending on the range the cell sum falls into I need the formula to
apply the appropriate multiplication factor and divide by 1000. I'm trying to
avoid manually inputting the (0.XX)/1000 based on the formula identifying
which range the sum falls into and automatically applying the appropriate
multiplication factor. In the example above the formula would be applied to
the A6 cell and identify the A5 sum as falling into the "range" and
automatically apply the corresponding multiplication factor.

Is something like this more advanced than what Excel can handle?

Thanks in advance for any perspective on this.

Is this type of formula possible in Excel?
 

KP wrote...
....
1 - 50,000,000.........................(0.17)/1000
50,000,001 - 100,000,000..........(0.13)/1000
100,000,001 - 250,000,000.........(0.10)/1000
250,000,001 - 500,000,000.........(0.08)/1000
500,000,001 - 750,000,000.........(0.07)/1000
750,000,000 ++ .......................(0.05)/1000

So depending on the range the cell sum falls into I need the formula to
apply the appropriate multiplication factor and divide by 1000. . . .
....

=A5*LOOKUP(A5,{0;50;100;250;500;750}*1000000,{.17; .13;.1;.08;.07;.05}/
1000)

Is this type of formula possible in Excel?
 

Enter this in a blank Col, say Col M,
1
50,000,001
100,000,001
250,000,001
500,000,001
750,000,000
then enter this in Col N
0.17
0.13
0.1
0.08
0.07
0.05
then assuming you have your values in Col A
enter this in B1 and copy down till end of your data set
=VLOOKUP(A1,M:N,2,TRUE)

This will give you the factor for the value in A1

You can also enter
=VLOOKUP(A1,M:N,2,TRUE)*A1/1000 to get the final result you want

Note the formula will fail if the cell in Col A contains a zero or
non-numeric value.

--
Always provide your feedback...


"KP" wrote:

I am trying to apply a multiplication factor that would vary depending on the
sum of a particular cell. For example let's say the value of cell A5 is the
SUM of A1:A4. Let's say the value is 255,000,000.

my multiplication factor table looks like this:

1 - 50,000,000.........................(0.17)/1000
50,000,001 - 100,000,000..........(0.13)/1000
100,000,001 - 250,000,000.........(0.10)/1000
250,000,001 - 500,000,000.........(0.08)/1000
500,000,001 - 750,000,000.........(0.07)/1000
750,000,000 ++ .......................(0.05)/1000

So depending on the range the cell sum falls into I need the formula to
apply the appropriate multiplication factor and divide by 1000. I'm trying to
avoid manually inputting the (0.XX)/1000 based on the formula identifying
which range the sum falls into and automatically applying the appropriate
multiplication factor. In the example above the formula would be applied to
the A6 cell and identify the A5 sum as falling into the "range" and
automatically apply the corresponding multiplication factor.

Is something like this more advanced than what Excel can handle?

Thanks in advance for any perspective on this.

Is this type of formula possible in Excel?
 

Hi,

And a combination of the previous ideas with some modifications:

In a range, say G1:H6 enter the table:

1 0.00017
50,000,001 0.00013
100,000,001 0.0001
250,000,001 0.00008
500,000,001 0.00007
750,000,000 0.00005

In another cell enter the formula:

=A5*LOOKUP(A5,G1:G6,H1:H6)


--
Thanks,
Shane Devenshire


"KP" wrote:

I am trying to apply a multiplication factor that would vary depending on the
sum of a particular cell. For example let's say the value of cell A5 is the
SUM of A1:A4. Let's say the value is 255,000,000.

my multiplication factor table looks like this:

1 - 50,000,000.........................(0.17)/1000
50,000,001 - 100,000,000..........(0.13)/1000
100,000,001 - 250,000,000.........(0.10)/1000
250,000,001 - 500,000,000.........(0.08)/1000
500,000,001 - 750,000,000.........(0.07)/1000
750,000,000 ++ .......................(0.05)/1000

So depending on the range the cell sum falls into I need the formula to
apply the appropriate multiplication factor and divide by 1000. I'm trying to
avoid manually inputting the (0.XX)/1000 based on the formula identifying
which range the sum falls into and automatically applying the appropriate
multiplication factor. In the example above the formula would be applied to
the A6 cell and identify the A5 sum as falling into the "range" and
automatically apply the corresponding multiplication factor.

Is something like this more advanced than what Excel can handle?

Thanks in advance for any perspective on this.