Tax computations with ceilings

No need to test for your ceiling -- just code it!
=MIN(50000,A1) will establish a $50,000 ceiling
regardless of the value of cell A1.

Hmm, this is taxing!

Could you post some example data with the "IF" formula and the calculation formula.

Seems that you may want to change the structure of the IF, right now you are using it to test for ceiling, but it may make more sense to use the IF formula to force appropriate calculations.

For instance, say formula A is for taxation if ceiling is not reached, and formula B is for taxation if ceiling is reached. The bones of the formula could go:
=if(ceiling criteria is reached,formula B,formula A)

but post your data, more knowledgeable people will help if they have examples with which to work!

Re: Hmm, this is taxing!

The threshold test formula is as follows:

=IF((YTD Wages - Current Wages) <= 7000,TRUE)

Where 7000 is the ceiling. Assuming the wages are 5000 per period and the tax rate is 5% the calculation for the first period would be 5000 * .05 for a tax of $250. The second peiod tax calc would be $2000 * .05 for a tax of $100. The second period taxable wages are 7000(ceiling) - 5000 (taxed in first period) for 2000 (taxable this period).

I hope this makes it clearer. Also I am not sure how coding the ceiling works could you provide more detail.

Thanks

RIch

D'OH! simple and elegant! :) (NT)

Idea and a couple of notes

Okay, if I've got this right, and I'm no Tax guy but I did stay at a Holiday Inn last night, then the ceiling thing works for the year right? Once the YTD income has gone beyond 7k, then the difference between 7k and each period income is taxed rather than the period income itself, but prior to that ceiling being reached, the poor b****** is taxed on full income each period.

So if all of your pay periods are in col(A),and you are going to calculate in col(B), you have headers in the first row and the data starts in row 2, try this in b2
=if(a2>7000,(7000-a2)*.05,a2*.05)
and from b3 down try
=if(sum(a$2:a3)>7000,(7000-a3)*.05,a3*.05)

Couple of answers- Mark W's "ceiling code" returns the minimum (=min) value of a set of numbers, in this case either the number for the pay period or the ceiling value, whichever is least, may or may not be that for which you are looking.

Second, your "if" function is curtailed- it could be doing more
the structure of "IF" is
"if this is true (or false), then do this, otherwise do this"
so your formula is missing the "otherwise do this" part
a simpler test is:
=(YTD wages-current wages)>=7000
which will return "true" if it is true and "false" if it is not

Hope that was in some way more helpful than confusing!

Try this...

I've re-read your posting (several times) and...

...now I'm uncertain about the nature of your
'YTD Wages' value. If your 'YTD Wages' values
is for *PRIOR* 'YTD Wages' (i.e., doesn't include
'Current Wages' then you should use my previously
posted formulation...

=MAX(0,MIN(7000-'YTD Wages','Current Wages'))*0.05

...however, if 'YTD Wages' *DOES INCLUDE* 'Current Wages'
then use...

=MAX(0,MIN(7000-'YTD Wages'+'Current Wages','Current Wages'))*0.05

Re: I've re-read your posting (several times) and...

The YTD does include current and the formula provided for that scenario works great -

Thanks for the help

Rich