Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formulation question | Charts and Charting in Excel | |||
If formulation: Is it possible... | Excel Discussion (Misc queries) | |||
If formulation | Excel Discussion (Misc queries) | |||
Time formulation? | Excel Discussion (Misc queries) | |||
cell in cell formulation possible??? | Excel Discussion (Misc queries) |