Joe's formula with Index and the regular SumProduct Formula and Manual calculations yield different amounts
at the very top of the brackets 600,000 700,000 800,000 etc.
No, they do not! Not for
my interpretation of the Egyptian
ambiguous tax tables, as I clearly explained in my posting #17.
You simply do not understand my presentation, as I tried to explain to you in PMs that you initiated.
-----
First, as I explained in a PM, the table and formulas in my posting #19 are my preferred choice. But for this discusssion, I will continue to refer to posting #17, as you do.
-----
Second, there is no need for the formula in B2 to be different from the formulas in C2:G2, other than the cell referenced for the total taxable income (a relative reference).
The difference in your B2 is only a matter of style. If that is your preference, it could be applied to C2:G2 as well. Likewise for B4 and C4:G4.
-----
Third, I previously explained
my interpretation of the Egyptian tables, to wit: ``I assume that "more than 600,000" means "
not less than 600,000". That is, "
greater than or equal to 600,000" ``.
Therefore, the income limits that I have in B9:G9 of the Egyptian tables (0, 600000, 700000 etc) are correct for
my interpretation, which is clearly labeled in B8 ("column for income
>=...").
If you interpret the Egyptian tables differently, that is your prerogative. But do not assert that
my formulas are incorrect because they do not work
your interpretation.
-----
Finally, as I explained in a PM, unlike the formulas B2:G2 -- which are the intended solution -- the "check" formulas in B4:G4 and B5:G5 are
tailored to the income limits that apply to the respective columns of the Egyptian tables.
Ergo, since column C of the Egyptian tables is for incomes
less than 700,000 (again, per
my interpretation), the largest value allowed in C1 is 699,999.99, not 700,000 as you insist on trying. Likewise for D1:G1, to wit: the limits are 799,999.99, 899,99.99 etc.
If you enter those top values into their respective columns, the results of the formulas in row 2, 4 and 5 agree.
Admittedly, B1 is an exception. Due to a simplification in the "check" formula in B5, the largest value allowed in B1 is 200,000, the top end of the 20% bracket.
So yes, the formula in B5 does not work for 599,999.99, which is
less than 600,000 (per
my interpretation).
But I implied that in my posting #17, where I wrote: `` The formula in B5
might need to be adjusted for different incomes in that range``.
Moreover, the "check" formula in B4 does agree with the intended solution in B2, even when B1 is 599,999.99.
In any case, those limits for B1:G1 are only for the formuls in B4:G4 and B5:G5. They do not apply to the formulas in B2:G2, because they are each fully-functional and fully generalalized.
The "check" formulas are intended to demonstrate the correctness of the formulas in B2:G2 by presenting a straight-forward implementation that is correct
for each respective column in the Egyptian table.
In other words, they are intended to simplify the calculations in order to demonstrate the intent of the INDEX/MATCH expression (to select the columns demonstrated in B4:G4) and the SUMPRODUCT formula overall (the "manual" calcuations demonstrated in B5:G5).
