Results 1 to 8 of 8

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. #1
    Board Regular
    Join Date
    Oct 2002
    Location
    California.
    Posts
    95

    Default 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???????????????????
    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.
    thanks,
    kristen

  2. #2
    Board Regular
    Join Date
    Apr 2003
    Location
    Jacksonville, FL
    Posts
    1,344

    Default Re: returning only positive values?

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

    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. #3
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,277

    Default Re: returning only positive values?

    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

  4. #4
    DRJ
    DRJ is offline
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,856

    Default

    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. #5
    Board Regular
    Join Date
    Oct 2002
    Location
    California.
    Posts
    95

    Default 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
    =

    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.
    thanks,
    kristen

  6. #6
    DRJ
    DRJ is offline
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,856

    Default

    Did you try my formula?

    =Max(your formual,0)

  7. #7
    Board Regular
    Join Date
    Oct 2002
    Location
    California.
    Posts
    95

    Default Re: returning only positive values?

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

  8. #8
    DRJ
    DRJ is offline
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,856

    Default

    Your Welcome

    Take Care

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com