Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Formula returns Error value - fix?

  1. #1

    Formula returns Error value - fix?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    year '000
    2012
    operating profit 1000
    admin costs 3000
    cos 210
    2013
    operating profit 1500
    admin costs 0
    cos 0
    I want 'average operating margin %'
    i.e. operating profit/(admin+cos)*100
    FORMULA RESULT BUT RESULT SHOULD BE
    =((C4/(C5+C6)*100)+(C9/(C10+C11)*100))/2 #DIV/0! 0.16
    My problem - because we are dividing by 0 at (C10+C11), my formula returns an error (as expected)
    What is the fix to my formula so it displays the correct result? Is it to use the logical IF and IS ERROR functions? If so please show me the fix
    nb I do not want to display any intermediate steps. I want to go straight from my data column to a column which displays the result
    Thank you.

  2. #2
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,321
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Up to XL 2003 = IF(ISERROR(your_formula),"",your_formula) Post 2003 versions =IFERROR(your_formula,"")

  3. #3
    I believe this would display only " " as opposed to the value im expecting.Thats exactly why im stuck., Logically this formula once it reaches
    IF true...then " "
    will not proceed to any calculation, i.e. the second part of the formula.

    ?

  4. #4
    Acolyte Sixthsense's Avatar
    Join Date
    Nov 2012
    Location
    India
    Posts
    26
    Articles
    0
    Hi jos,

    Please show it in excel

  5. #5
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,321
    Articles
    0
    Excel Version
    2010 on Xubuntu
    How do you obtain 0.16 with the given data ?

  6. #6
    @Pecorflyer I got 0.16 as follows
    [1000/(3000+210) + 1500/(0+0)] / 2

    Also, as I'm using Excel 2010, I tried your suggestion =IFERROR(your_formula,""). It does not work for what I want to achieve. Either that or I am not applying it correctly. Anyway, I think maybe it's better to abandon this made up example and to show an extract from my real life spreadsheet instead. See new thread entitled
    Formula returns Error value - fix? (reposted)

  7. #7

    Formula returns Error value - fix? (reposted with attachment)

    My formula, in the attached spreadsheet, returns an error value, as shown.
    However, each time the scenario is as given, I would like to return a value based on a modification of the original formula. I hope my notes in the attachment express adequately my requirement.

    Thank you
    Attached Files Attached Files

  8. #8
    Acolyte Sixthsense's Avatar
    Join Date
    Nov 2012
    Location
    India
    Posts
    26
    Articles
    0
    Theerrors in your formula is highlighted below

    In E5 Cell

    n.a.

    In F5 Cell
    n.a.

    In G5 Cell
    n.a.

    You AA5 Cell Formula

    =SUM(((7/12*J5)/(7/12*(K5+L5)))*7+((5/12*E5)/(5/12*(F5+G5)))*5 )/12*100

  9. #9
    @sixthsense. Thank you. I am aware E5, F5 and G5 are picking up n.a.'s

    So I want to know is, what do I have to do to my formula, using any logical functions available in excel, to give the instruction, IF my formula "=SUM(((7/12*J5)/(7/12*(K5+L5)))*7+((5/12*E5)/(5/12*(F5+G5)))*5 )/12*100" results in an error value, then perform the alternative formula "E5/(F5+G5)*100"

    Please can anyone help?

  10. #10
    sorted! see attachment
    thank you for your contributions....
    Attached Files Attached Files

Page 1 of 2 1 2 LastLast

Posting Permissions

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