formulas

  • Thread starter Thread starter mike
  • Start date Start date
M

mike

I'm trying to get a spread sheet to do the following:

Enter $243,323 (for example)

Now i want the first $100,000 to be multiplied by 5% and
the next $100,000 to be multiplied by 6%, and the
remaining $43,323 to be multipled by 7%. However, i'm
trying to set it up so you only need to enter a value in
one cell ($243,323 entered in cell A1) to get it to do
all the calculations. Any ideas?
 
-----Original Message-----
I'm trying to get a spread sheet to do the following:

Enter $243,323 (for example)

Now i want the first $100,000 to be multiplied by 5% and
the next $100,000 to be multiplied by 6%, and the
remaining $43,323 to be multipled by 7%. However, i'm
trying to set it up so you only need to enter a value in
one cell ($243,323 entered in cell A1) to get it to do
all the calculations. Any ideas?
.
If you enter 100000 in let's say A6, 243,323 in A7 and
Click to expand...
the following formula in B7, B8 and B9, I think you'll get
it.

Formula: =IF($A$7/(B6*$A$6)>2,$A$6*0.05,IF($A$7/(B6*$A$6)
1,$A$6*0.06,IF($A$7/(B6*$A$6)>0,($A$7-INT($A$7/$A$6)*$A$6)
Click to expand...
*0.07)))
 
mike said:
I'm trying to get a spread sheet to do the following:

Enter $243,323 (for example)

Now i want the first $100,000 to be multiplied by 5% and
the next $100,000 to be multiplied by 6%, and the
remaining $43,323 to be multipled by 7%. However, i'm
trying to set it up so you only need to enter a value in
one cell ($243,323 entered in cell A1) to get it to do
all the calculations. Any ideas?
Click to expand...

Another alternative.

=SUMPRODUCT(IF(A1<{100000;200000;TRUE},A1,{100000;200000;TRUE})
-IF(A1<{0;100000;200000},A1,{0;100000;200000}),{0.05;0.06;0.07})
 
Back
Top