Cell Formulation

[tracyjollf]

Can a cell be formulated to reflect a value based on the following situation?

Cell C5 is $34000.00; the first $1600.00 of that amount + the value of C7
needs to be multiplied * 2.25%. That value will be in cell C10. ALSO,
Cell C5 is $34000.00; the second $1600.00 up to $3200.00 needs to be
multiplied * 2.75%. That value will be in cell C11.

EXAMPLE: $34000.00
1st level 1600.00 * 2.25% = $36.00
2nd level 1600.00 * 2.75% = $44.00

ANOTHER EXAMPLE: $3000.00
1st level 1600.00 * 2.25% = $36.00
2nd level 1400.00 * 2.75% = $38.50

I am using Excel 2003 version.

 

[akphidelt]

Try this for the first level

=If((C5-1600)>0,1600*.0225,(1600-C5)*.0225)

For the second level

=If((C5-3200)>0,1600*.0275,If(C5>1600,(3200-C5)*.0275,0))

This is untested, but maybe it will work, haha

 

[akphidelt]

I just reread my post and I have a feeling I made a mistake because

3200-C5 in your example would produce 200, when really you want 3000-1600 to
give you 1400... so to fix that

Change the line that says 3200-C5 to C5-1600. Then it should work. Sorry

 

[T. Valko]

You say:

Cell C5 is $34000.00; the first $1600.00 of that amount
+ the value of C7 needs to be multiplied * 2.25%

But your examples don't show the addition of cell C7:

1st level 1600.00 * 2.25% = $36.00

Try these:

1st level:

=IF(COUNT(C5),MIN(1600,C5)*2.25%,0)

2nd level:

=IF(C5>1600,MIN(1600,C5-1600)*2.75%,0)

 

[tracyjollf]

THE FIRST LEVEL WORKED PERFECT.

THE SECOND LEVEL HOWEVER, YOU DID NOT INCLUDE CELL C7 TO BE ADDED TO THE C5
BALANCE. WILL YOU PLEASE REREAD AND ADVISE?

THANK YOU SO VERY MUCH.

 

[tracyjollf]

I THINK I FIGURED OUT ADDING IN THE C7 CELL. THE ONLY PROBLEM NOW IS WHEN I
TEST THE FORMULA BY USING A SMALL FIGURE IE: $3000, IT IS NOT CALCULATING
CORRECTLY.

AS LONG AS THE ORIGINAL AMOUNT IS OVER $3200 TOTAL, THE $36 OR $44 FIGURE IS
CALCULATING PROPERLY. ANYTHING UNDER $3200 IS NOT CALCULATING CORRECTLY.

PLEASE ADVISE AT YOUR CONVENIENCE.
THANKS