Remember Me?

 tracyjollf external usenet poster First recorded activity by ExcelBanter: Apr 2008 Posts: 3 Cell Formulation

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 external usenet poster First recorded activity by ExcelBanter: Apr 2007 Posts: 461 Cell Formulation

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(C51600,(3200-C5)*.0275,0))

This is untested, but maybe it will work, haha

"tracyjollf" wrote:

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 external usenet poster First recorded activity by ExcelBanter: Apr 2007 Posts: 461 Cell Formulation

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

"akphidelt" wrote:

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(C51600,(3200-C5)*.0275,0))

This is untested, but maybe it will work, haha

"tracyjollf" wrote:

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.

 T. Valko external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 15,768 Cell Formulation

You say:

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

1st level 1600.00 * 2.25% = \$36.00

Try these:

1st level:

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

2nd level:

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

--
Biff
Microsoft Excel MVP

"tracyjollf" wrote in message
...
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.

 tracyjollf external usenet poster First recorded activity by ExcelBanter: Apr 2008 Posts: 3 Cell Formulation

THE FIRST LEVEL WORKED PERFECT.

THE SECOND LEVEL HOWEVER, YOU DID NOT INCLUDE CELL C7 TO BE ADDED TO THE C5

THANK YOU SO VERY MUCH.
--
Tracy Jo LLF

"tracyjollf" wrote:

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.

 tracyjollf external usenet poster First recorded activity by ExcelBanter: Apr 2008 Posts: 3 Cell Formulation

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.

THANKS
--
Tracy Jo LLF

"tracyjollf" wrote:

THE FIRST LEVEL WORKED PERFECT.

THE SECOND LEVEL HOWEVER, YOU DID NOT INCLUDE CELL C7 TO BE ADDED TO THE C5

THANK YOU SO VERY MUCH.
--
Tracy Jo LLF

"tracyjollf" wrote:

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.

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post TJ Bartel Charts and Charting in Excel 2 March 5th 07 01:52 PM OSCAR Excel Discussion (Misc queries) 3 December 31st 05 08:53 AM OSCAR Excel Discussion (Misc queries) 2 December 31st 05 07:06 AM LGrobe Excel Discussion (Misc queries) 1 August 16th 05 06:23 PM Abi Excel Discussion (Misc queries) 4 January 7th 05 08:13 PM

All times are GMT +1. The time now is 07:00 PM. Copyright ©2004-2021 ExcelBanter.