Need help with formula

[Tia]

I have a payroll spreadsheet that has the following formula to calculate
SUTA. It worked great until an employee capped out on the amount taxed for
SUTa then I get a REF! error in the cell. The following is located in column
H.
=IF(G7<26700, F7,IF(G7-F7>262700,0,(F7(F7-26700))))*0.026

G7=YTD Payroll
F7=Payroll w/0 deductions
H7=SUTA Calculation

The employee had the following information
G6=25200.00
F6=6000.00
H6=156.00

G7=31800.00
F7=6600.00
H7=REF!!

 

[Mike H]

Hi,

I can't answer your question fully because I have no idea what capped out
for SUTA means but is clear is from the data you have given that all
expressions evaluate as False so your formula is evaluating the expression

F7(F7-2600)

Which is giving the REF error. What would you like it to do?

Mike

 

[Mike H]

I meant

F7(F7-26700)

Hi,

I can't answer your question fully because I have no idea what capped out
for SUTA means but is clear is from the data you have given that all
expressions evaluate as False so your formula is evaluating the expression

F7(F7-2600)

Which is giving the REF error. What would you like it to do?

Mike

 

[Ron Coderre]

Try this:
H6: =MIN(MAX(27600-(G6-F6),0),F6)*0.026

Copy that formula down as far as you need

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Ron Coderre]

One more thing....
If you want to copy the formula down through rows
where Col_F may have no values,

use this variation:
H6: =IF(F6>0,MIN(MAX(27600-(G6-F6),0),F6)*0.026,0)

or this:
H6: =(F6>0)*MIN(MAX(27600-(G6-F6),0),F6)*0.026

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Tia]

I apologize for not being more clear. Suta is calculated on income up to
$26700. So when a income is below that the formula worked fine. Once the
employees income surpasses it, than it needs to still calculate SUTA on the
amount up to 26700. So for example on the 1st payroll, an employee made
$21000, it's just that amount times by the 0.026(SUTA). The second payroll
the employee made $3100. The formula would need to pay the 0.026 on the
amount from $21000 to $26700. Then on the third payroll, the SUTA would
calculate to 0.

So in the portion of the formula that isn't working, it is suppose to have
evaluated the difference of the amount already paid on up to the $26700.
Hopefully, that helps.

Thanks for your help.

 

[Sandy Mann]

I assume that your data is laid out as follows:

Column F: Monthly Payrolls say from F5 to, (eventually), F17
Column G: Running Total of Payroll
Column H: Calculation of SUTA whatever that is.

In H5 enter:

=IF(G6<>"",MIN(26700,G6)*0.026,"")

In H6 enter:

=IF(G6<>"",MAX(0,MIN(26700,G6)-G5)*0.026,"")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Ron Coderre]

Sandy

I believe the data is structure this way:
Col_F: Current Payroll Amount for Each Employee)
Col_G: Year-to-Date Payroll Amt for Each Employee

Col_H: State Unemployment Tax calculation for the current payroll
which is 2.6% of the first $26,700 of earnings.

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Sandy Mann]

Hi Ron,

That is what I thought even if it was not what I said, wasn't it?

Is the limit 26700? because both your formulas have 27600

When I posted I had already past your post but I could not see Tia's & Mike
H's posts until I did a Tools > Get Next 300 Headers when they suddenly
popped up otherwise I would not have tried to re-invent the wheel.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Ron Coderre]

Sandy Mann pointed out that my formula may have an incorrect limit.

So...if the limit is 26700

Then these options should work:

H6: =IF(F6>0,MIN(MAX(26700-(G6-F6),0),F6)*0.026,0)
or...
H6: =(F6>0)*MIN(MAX(26700-(G6-F6),0),F6)*0.026

Hopefully one of my posts will help.

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Ron Coderre]

Is the limit 26700? because both your formulas have 27600

Heck, I don't know...the OP had some irregularities
and I guess I just read it wrong. In any case, I
sent another post with the new number.

Thanks for spotting the problem.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)