1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Excel Help!!

Discussion in 'Windows & Other Software' started by Teifiterror, 3 May 2006.

  1. Teifiterror

    Wise Guy

    Joined: 18 Oct 2002

    Posts: 1,155

    Location: Bromsgrove

    Hi there, ive got a pretty difficult problem with excel that I just cant seem to work out.

    I have a cell with an equation in it, and the result given in the cell can sometimes return an error depending on the values used. What I would like to do is have the cell work so that if an error is shown it shows it as the value zero in the cell itself. But if the value of the equation isnt an error I would like it to show the result of that equation.

    I would also like it to display a zero when there is no information in the sheet i.e. when the error is #DIV/0!

    Any help is appreciated!
     
  2. div0

    Mobster

    Joined: 12 Jan 2006

    Posts: 3,600

    Location: Edinburgh

    This is what you want....(i think)

    =IF(ISERROR(formula), 0, formula)

    Where formula is the equation in the cell that MAY return an error.

    For example:

    A1 = 2, B1 = 4
    A2 = 3, B2 = 0
    A3 = 6, B3 = text

    Now say in column C your FORMULA is:

    Cx = Ax/Bx (eg C1 =A1/B1)

    You will get the following results:

    C1 = 0.5
    C2 = #DIV/0!
    C3 = #VALUE!

    BUT if you change Column C to be:

    =IF(ISERROR(Ax/Bx), 0, Ax/Bx) (eg C1 =IF(ISERROR(A1/B1), 0, A1/B1)

    Then you will get the following results:

    C1 = 0.5
    C2 = 0
    C3 = 0

    Try it - it sounds like it'll do what you're asking!! :)

    Hope it helps :)
     
  3. Teifiterror

    Wise Guy

    Joined: 18 Oct 2002

    Posts: 1,155

    Location: Bromsgrove

    THANKS!! Just what I was after.