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-F7262700,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!!

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

"Tia" wrote:

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-F7262700,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!!

I meant

F7(F7-26700)

"Mike H" wrote:

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

"Tia" wrote:

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-F7262700,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!!

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)

"Tia" wrote in message
...
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-F7262700,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!!

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(F60,MIN(MAX(27600-(G6-F6),0),F6)*0.026,0)

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

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

Regards,

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

"Ron Coderre" wrote in message
...
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)

"Tia" wrote in message
...
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-F7262700,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!!

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.


"Mike H" wrote:

I meant

F7(F7-26700)

"Mike H" wrote:

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

"Tia" wrote:

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-F7262700,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!!

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


Replace @mailinator.com with @tiscali.co.uk


"Tia" wrote in message
...
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.


"Mike H" wrote:

I meant

F7(F7-26700)

"Mike H" wrote:

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

"Tia" wrote:

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-F7262700,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!!

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" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"Tia" wrote in message
...
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.


"Mike H" wrote:

I meant

F7(F7-26700)

"Mike H" wrote:

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

"Tia" wrote:

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-F7262700,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!!

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


Replace @mailinator.com with @tiscali.co.uk


"Ron Coderre" wrote in message
...
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" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"Tia" wrote in message
...
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.


"Mike H" wrote:

I meant

F7(F7-26700)

"Mike H" wrote:

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

"Tia" wrote:

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-F7262700,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!!

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(F60,MIN(MAX(26700-(G6-F6),0),F6)*0.026,0)
or...
H6: =(F60)*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" wrote in message
...
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(F60,MIN(MAX(27600-(G6-F6),0),F6)*0.026,0)

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

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

Regards,

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

"Ron Coderre" wrote in message
...
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)

"Tia" wrote in message
...
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-F7262700,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!!

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)



"Sandy Mann" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"Ron Coderre" wrote in message
...
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" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"Tia" wrote in message
...
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.


"Mike H" wrote:

I meant

F7(F7-26700)

"Mike H" wrote:

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

"Tia" wrote:

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-F7262700,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!!