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,

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,