Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Then condition on Values
Guru's:
I have a situation in two columns "A4" & "B4". in column "A4" & "B4" I need to calulate percentages as follows, it's kinda like a TAX table where column "H" would be a single person (one Income) and column "I" would be a married couple (both working/two incomes). Thix may give you a better idea of the structure I'm looking for! 1.) If the values in column "A4" are between 12,000 and 13,360 take 0% of value, however, if the values in column "B4" is between 12,000 and 13,360, take 3,12%. 2.) If the values in column "A5" are between 13,360 and 14,660 take 2.6% of value, however, if the values in column "B5" is between 13,360 and 14,660 , take 3,64%. 3.) If the values in column "A6" are between 14,660 and 16,010 take 3.12% of value, however, if the values in column "B6" is between 14,660 and 16,010 , take 4.16%. I guess columns "A", "B", "H" & "I" would a table list of values on a another hidden page/sheet which can/would be updatable. But I need the code to look up and down each row and column, then do a calculation against cell values in column "AN" and show the results in the cell on the right in column "AM" Example of column lay-out: A B H I 12,000 13,360 0% 3.12% 13,360 14,660 2.60% 3.64% 14,660 16,010 3.12% 4.16% 16,010 17,340 4.16% 4.68% 17,340 19,990 4.16% 5,20% I hope that this is not too much and/or confusing. If there are questions, please, post them here! I am open for suggestions on the best approach on this as well! Thanks, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Then condition on Values
I put the tax branket at cell F3 = FROM
FROM TO SINGLE MARRIED 12000.00 13359.99 0% 3.12% 13360.00 14659.99 2.60% 3.64% 14660.00 16009.99 3.12% 4.16% 16010.00 17339.99 4.16% 4.68% 17340.00 19989.99 4.17% 5.20% 19990.00 OVER 5.00% 6.20% COLUMN A B A1 : SINGLE B1 : MARRIED A2 : INPUT THE INCOME OF SINGLE PERSON B2: INPUT THE INCOME OF MARRIED PERSON A4 INPUT THE FOLLOWING FORMULA: =IF(AND($A$2F4,$A$2<G4),H4,IF(AND($A$2F5,$A$2<G5 ),H5,IF(AND($A$2F6,$A$2<G6),H6,IF(AND($A$2F7,$A$ 2<G7),H7,IF(AND($A$2F8,$A$2<G8),H8,IF($A$2<F4,0,H 9)))))) B4 INPUT THE FOLLOWING FORMULA: =IF(AND($B$2F4,$B$2<G4),I4,IF(AND($B$2F5,$B$2<G5 ),I5,IF(AND($B$2F6,$B$2<G6),I6,IF(AND($B$2F7,$B$ 2<G7),I7,IF(AND($B$2F8,$B$2<G8),I8,IF($B$2<F4,0,I 9)))))) IF THE ABOVE FORMULA is what you are looking for, you may combine two formula into a cell, if a2 is blank, use a4 formula, otherwise, use b4 forumla. Hope it helps "Jay" wrote: Guru's: I have a situation in two columns "A4" & "B4". in column "A4" & "B4" I need to calulate percentages as follows, it's kinda like a TAX table where column "H" would be a single person (one Income) and column "I" would be a married couple (both working/two incomes). Thix may give you a better idea of the structure I'm looking for! 1.) If the values in column "A4" are between 12,000 and 13,360 take 0% of value, however, if the values in column "B4" is between 12,000 and 13,360, take 3,12%. 2.) If the values in column "A5" are between 13,360 and 14,660 take 2.6% of value, however, if the values in column "B5" is between 13,360 and 14,660 , take 3,64%. 3.) If the values in column "A6" are between 14,660 and 16,010 take 3.12% of value, however, if the values in column "B6" is between 14,660 and 16,010 , take 4.16%. I guess columns "A", "B", "H" & "I" would a table list of values on a another hidden page/sheet which can/would be updatable. But I need the code to look up and down each row and column, then do a calculation against cell values in column "AN" and show the results in the cell on the right in column "AM" Example of column lay-out: A B H I 12,000 13,360 0% 3.12% 13,360 14,660 2.60% 3.64% 14,660 16,010 3.12% 4.16% 16,010 17,340 4.16% 4.68% 17,340 19,990 4.16% 5,20% I hope that this is not too much and/or confusing. If there are questions, please, post them here! I am open for suggestions on the best approach on this as well! Thanks, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional sum, when condition has multiple values | Excel Worksheet Functions | |||
How to add cell values until a condition changes | Excel Worksheet Functions | |||
SUM OF NUM VALUES WITH CONDITION | Excel Discussion (Misc queries) | |||
Counting Values on a condition | Excel Worksheet Functions | |||
Cannot sum values based on condition | Excel Worksheet Functions |