# returning only positive values?

This is a discussion on returning only positive values? within the Excel Questions forums, part of the Question Forums category; Hi i have a quick question for anyone. I am currently using excel for a project in my payroll class, ...

1. ## returning only positive values?

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???????????????????
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.

2. ## Re: returning only positive values?

If functions work like IF...Then...Else or If(test_condition,if_true, if_false)

Code:
=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)

3. ## Re: returning only positive values?

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

4. 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.

5. ## Re: returning only positive values?

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.

******** ******************** ************************************************************************>
 Microsoft Excel - Comprehensive Payroll Problem - K Plaza 3.xls ___Running: 11.0 : OS = Windows XP
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 Y6Z6AA6AB6Y7Z7AA7AB7Y8Z8AA8AB8Y9Z9AA9AB9Y10Z10AA10AB10Y12Z12AA12AB12Y13Z13AA13AB13Y14Z14AA14AB14Y15Z15AA15AB15Y16Z16AA16AB16Y18Z18AA18AB18Y19Z19AA19AB19Y20Z20AA20AB20Y21Z21AA21AB21Y22Z22AA22AB22Y24Z24AA24AB24Y25Z25AA25AB25Y26Z26AA26AB26Y27Z27AA27AB27Y28Z28AA28AB28 =

Y
Z
AA
AB
4
SUIETTFUTA
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 *

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

6. Did you try my formula?

7. ## Re: returning only positive values?

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

Take Care

9. ## Re: returning only positive values?

Originally Posted by DRJ
Did you try my formula?

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•