#1   Report Post  
Old April 18th 08, 09:13 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2008
Posts: 3
Default 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.

  #2   Report Post  
Old April 18th 08, 09:38 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
Posts: 461
Default 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.

  #3   Report Post  
Old April 18th 08, 09:42 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
Posts: 461
Default 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.

  #4   Report Post  
Old April 18th 08, 09:52 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default 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%


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(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.



  #5   Report Post  
Old April 18th 08, 10:30 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2008
Posts: 3
Default Cell Formulation

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.
--
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.



  #6   Report Post  
Old April 18th 08, 11:09 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2008
Posts: 3
Default 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.

PLEASE ADVISE AT YOUR CONVENIENCE.
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
BALANCE. WILL YOU PLEASE REREAD AND ADVISE?

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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formulation question TJ Bartel Charts and Charting in Excel 2 March 5th 07 01:52 PM
If formulation: Is it possible... OSCAR Excel Discussion (Misc queries) 3 December 31st 05 08:53 AM
If formulation OSCAR Excel Discussion (Misc queries) 2 December 31st 05 07:06 AM
Time formulation? LGrobe Excel Discussion (Misc queries) 1 August 16th 05 06:23 PM
cell in cell formulation possible??? Abi Excel Discussion (Misc queries) 4 January 7th 05 08:13 PM


All times are GMT +1. The time now is 07:00 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017