Harold Coates
Level 4 (1,394 points)
Applications

Q: Spreadsheet ' if ' function

AW spreadsheet question...

 

I am trying to remember a formula for making minus answers show zero

Best I remember it went similar to this:   =(if minus then...0)

 

Appreciate any help,

 

Harold

iMac (21.5-inch Mid 2011), Mac OS X (10.6.8), quad 2.7ghz 16GB and 17 inch MB Pro

Posted on Dec 6, 2013 2:21 PM

by Niel,Solvedanswer
Niel Niel
Level 10 (314,412 points)
Mac OS X
A:

Use the following:

 

=IF((B33-B32)<0,0,B33-B32)

 

(94167)

Posted on Dec 6, 2013 8:50 PM

Close
Harold Coates

Q: Spreadsheet ' if ' function

  • All replies
  • Helpful answers

  • by Niel,

    Niel Niel Dec 6, 2013 2:26 PM in response to Harold Coates
    Level 10 (314,412 points)
    Mac OS X
    Dec 6, 2013 2:26 PM in response to Harold Coates

    In a different cell, use:

     

    =IF(A1<0,0,A1)

     

    (94136)

  • by Harold Coates,

    Harold Coates Harold Coates Dec 6, 2013 6:31 PM in response to Niel
    Level 4 (1,394 points)
    Applications
    Dec 6, 2013 6:31 PM in response to Niel

    Thanks for responding,

     

    I'm not sure what you mean by 'in a different cell'.

     

    The cell contains the sum of two cells, then I would like that if the answer is minus then the display would show zero rather than the minus figure.

     

    Harold

  • by Niel,

    Niel Niel Dec 6, 2013 6:37 PM in response to Harold Coates
    Level 10 (314,412 points)
    Mac OS X
    Dec 6, 2013 6:37 PM in response to Harold Coates

    You need to put that formula in a separate cell from the one that contains the result. Alternatively, put the rest of the formula where both instances of A1 are.

     

    (94153)

  • by Barry,

    Barry Barry Dec 6, 2013 6:40 PM in response to Harold Coates
    Level 7 (32,697 points)
    iWork
    Dec 6, 2013 6:40 PM in response to Harold Coates

    Hi Harold,

     

    For the example, the base formula adds the contents of cells A2 and B2. Replace "A2+B2" with your formula.

     

    =MAX(0,A2+B2)

     

    Regards,

    Barry

  • by Harold Coates,

    Harold Coates Harold Coates Dec 6, 2013 7:01 PM in response to Barry
    Level 4 (1,394 points)
    Applications
    Dec 6, 2013 7:01 PM in response to Barry

    Maybe this will give a better idea if the graph holds together

     

                        Milage          Amt.           Gal.            Miles                 MPG

    11/1326457.347.9116.526377.122.8
    11/1426836.854.4018.764379.520.2
    11/1527218.350.1416.720381.522.8
    11/2927521.150.0016.671302.818.2
    -27521.1#DIV/0!
    0#DIV/0!
    0#DIV/0!

     

    My goal is to replace  #DIV/0 with 0 until the calculation is complete with the rest of its numbers.

  • by Niel,

    Niel Niel Dec 6, 2013 7:09 PM in response to Harold Coates
    Level 10 (314,412 points)
    Mac OS X
    Dec 6, 2013 7:09 PM in response to Harold Coates

    Use a formula such as:

     

    =IF(ISERROR(A1),0,A1)

     

    replacing both instances of A1 with the formula currently used in the MPG cells.

     

    (94154)

  • by Harold Coates,

    Harold Coates Harold Coates Dec 6, 2013 7:22 PM in response to Niel
    Level 4 (1,394 points)
    Applications
    Dec 6, 2013 7:22 PM in response to Niel

    I'm sorry but I don't know what A1 represents

  • by Niel,

    Niel Niel Dec 6, 2013 7:26 PM in response to Harold Coates
    Level 10 (314,412 points)
    Mac OS X
    Dec 6, 2013 7:26 PM in response to Harold Coates

    It doesn't matter what A1 represents; it's just a placeholder. Replace it as described above.

     

    (94156)

  • by Harold Coates,

    Harold Coates Harold Coates Dec 6, 2013 7:44 PM in response to Niel
    Level 4 (1,394 points)
    Applications
    Dec 6, 2013 7:44 PM in response to Niel

    Are you saying place this:  =SUM(E33/D33)  to replace each instances of A1 ?

     

    Ref:  Column E  is  Miles and  F  is  MPG

  • by Niel,

    Niel Niel Dec 6, 2013 7:46 PM in response to Harold Coates
    Level 10 (314,412 points)
    Mac OS X
    Dec 6, 2013 7:46 PM in response to Harold Coates

    Yes, except for the = sign at the beginning. The finished formula is:

     

    =IF(ISERROR(SUM(E33/D33)),0,SUM(E33/D33))

     

    (94162)

  • by Harold Coates,

    Harold Coates Harold Coates Dec 6, 2013 8:46 PM in response to Niel
    Level 4 (1,394 points)
    Applications
    Dec 6, 2013 8:46 PM in response to Niel

    That worked..... thanks

     

    Now do you have an idea about the column E (Miles) figure until the input from B Milage is inserted?

     

    Miles formula   =SUM(B33-B32)

     

                    Milage        Amount        Gal.            Miles            MPG

    11/1326457.347.9116.53377.122.8
    11/1426836.854.4018.76379.520.2
    11/1527218.350.1416.72381.522.8
    11/2927521.150.0016.67302.818.2
    -27521.10
    00
    00
    00
    00
    00
    00
    00
    00
    00
    1226.36387.7277871.120.3

  • by Niel,Solvedanswer

    Niel Niel Dec 6, 2013 8:50 PM in response to Harold Coates
    Level 10 (314,412 points)
    Mac OS X
    Dec 6, 2013 8:50 PM in response to Harold Coates

    Use the following:

     

    =IF((B33-B32)<0,0,B33-B32)

     

    (94167)

  • by Harold Coates,

    Harold Coates Harold Coates Dec 6, 2013 9:16 PM in response to Niel
    Level 4 (1,394 points)
    Applications
    Dec 6, 2013 9:16 PM in response to Niel

    Thanks so much.

     

    I have been burdened with that problem for about three years with 3 vehicles.  I keep up with my personal vehicles just for... the heck of it.

    It does help remembering when and where we went.  Other columns tell about trips and just notes like oil changes, etc.

     

    Have a great day and Christmas,

     

    Harold

  • by Barry,

    Barry Barry Dec 7, 2013 3:41 PM in response to Harold Coates
    Level 7 (32,697 points)
    iWork
    Dec 7, 2013 3:41 PM in response to Harold Coates

    Hi Harold,

     

    My preferences with issues of thiis type is to display 'empty' cells rather than a string of zeroes in the rows where data has not been entered. Doing so produces a table tht looks like this:

    Screen Shot 2013-12-07 at 3.25.12 PM.png

    (This was done in Numbers, as AppleWorks is not installed on the machine I'm using right now. The formulas will be the same, though.

     

    The formula for column E (Miles) is entered in E3, then filled down to E15:

     

    E3: =IF(LEN(B3)>0,B3-B2,"")

     

    The formula for column F (mpg) does not reference a cell in a previous row, so it may be entered in F2 and filled down from there to F16:

     

    F2: =IF(LEN(D2)>0,E2/D2,"")

     

    Both formulas use the LEN() function to determne if data has been entered into a required cell (B3 or D2). If so, the calculation is carried out, and the result inserted in the cell containing the formula. If not, a null string ( "" ) is inserted in the cell, making it appear empty.

     

    Row 16 contains a SUM formula in Columns C, D and E. Enter the formula below in C16, then fill it right to E16:

     

    =SUM(C2:C15

     

     

    Sums in these cells are different from those in your sample as they consider only the vlues shown on the table.

     

    Regards,

    Barry