returning only positive values?

[NuWest]

Hi i have a quick question for anyone. I am currently using excel for a project in my payroll class, where we were give, employee salary abouts and 4 pay dates and then we have to re-create employee registers and payroll registers. my problem is in the payroll register sheet, specifically the portion that shows taxable earnings. I am currently using the formula:

=IF(SUM('Hunter, James'!$I$12:$I$16)<'Company Information'!$C$13,SUM('Hunter, James'!$I$12:$I$16),('Company Information'!$C%13-SUM('Hunter, James'!$I$12:$I$16)))

but the problem with that is that once an employee has gone over a limit for taxable earnings, this formula returns a negative number. instead of returning a negative number i want it to just return 0. but other than that this formula works spectacular for all employees that have NOT hit the taxable limit yet.

any help anyone???????????????????
thanks in advance.
kristen.

edit---in the formula above,
"'Hunter, James'!$I$12:$I$16" refers to the total of the wages paid so far in the year.
"'Company Information'!$C$13" refers to the actual taxable limit.

 

[mdmilner]

If functions work like IF...Then...Else or If(test_condition,if_true, if_false)
Add another comma and just add 0

=IF(SUM('Hunter, James'!$I$12:$I$16)<'Company Information'!$C$13,SUM('Hunter, James'!$I$12:$I$16),('Company Information'!$C%13-SUM('Hunter, James'!$I$12:$I$16)),0)

 

[Smitty]

Why not add another condition to your formula IF(SUM <=0,0,ELSE)

Although posting an example of youir sheet will get you a cleaner formula.

And is there any way to get the hardcoded name out of your formula and reference an employee table? Any turnover whatsoever will have you manually recalculating all of your formulas by hand.

Hope that helps,

Smitty

 

[DRJ]

Try this

=Max(=IF(SUM('Hunter, James'!$I$12:$I$16)<'Company Information'!$C$13,SUM('Hunter, James'!$I$12:$I$16),('Company Information'!$C%13-SUM('Hunter, James'!$I$12:$I$16))),0)

So it will return the max of the number and zero.

 

[NuWest]

this is what the portion of the spreadsheet i am having trouble looks like. and i am not sure i know what "hardcoded" names are. if it is reference to the employees name, each employee is set on a seperate sheet that only includes their personal payroll data. and the name of their sheets are their names. i should also note, that some of the formulas in the first column are incorrect, i just havent corrected them yet.... the formua i am working with is currently in Z20.

Comprehensive Payroll Problem - K Plaza 3.xls
	Y	Z	AA	AB
4	SUI	ETT	FUTA
5
6	$1,825.00	$1,825.00	$1,825.00	1
7	$2,245.38	$2,245.38	$2,245.38	2
8	$2,250.00	$2,250.00	$2,250.00	3
9	$905.77	$905.77	$905.77	4
10	$7,226.15	$7,226.15	$7,226.15	5
11
12	$3,681.59	$3,681.59	$3,681.59	6
13	$4,345.38	$4,345.38	$4,345.38	7
14	$4,500.00	$4,500.00	$2,250.00	8
15	$1,655.77	$1,655.77	$1,655.77	9
16	$14,182.74	$14,182.74	$11,932.74	10
17
18	$5,506.59	$5,506.59	$5,506.59	11
19	$6,445.38	$6,445.38	$6,445.38	12
20	$-	$(250.00)	$-	13
21	$2,405.77	$2,405.77	$2,405.77	14
22	$14,357.74	$14,107.74	$14,357.74	15
23
24	$-	$-	$-	16
25	$-	$-	$-	17
26	$-	$-	$-	18
27	$3,155.77	$3,155.77	$3,155.77	19
28	$3,155.77	$3,155.77	$3,155.77	20
Payroll Register

 

[DRJ]

Did you try my formula?

=Max(your formual,0)

 

[NuWest]

the =max(my formula,0) works just dandy! THANKS!

 

[DRJ]

Your Welcome

Take Care

 

[willow-mouse]

Did you try my formula?

=Max(your formual,0)

This thread is 12 years old, but I didn't think of doing this - genius!