Formula returns Error value - fix?

jos

New member
Joined
Feb 28, 2014
Messages
7
Reaction score
0
Points
0
year £'000
2012
operating profit1000
admin costs3000
cos210
2013
operating profit1500
admin costs0
cos0
I want 'average operating margin %'
i.e. operating profit/(admin+cos)*100
FORMULARESULTBUT 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.
 

Pecoflyer

Admin Alumnus
Joined
Oct 13, 2011
Messages
1,779
Reaction score
0
Points
36
Location
Brussels Belgium
Excel Version(s)
2010 on Xubuntu - O365
Up to XL 2003 = IF(ISERROR(your_formula),"",your_formula) Post 2003 versions =IFERROR(your_formula,"")
 

jos

New member
Joined
Feb 28, 2014
Messages
7
Reaction score
0
Points
0
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.

?
 

jos

New member
Joined
Feb 28, 2014
Messages
7
Reaction score
0
Points
0
@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)
 

jos

New member
Joined
Feb 28, 2014
Messages
7
Reaction score
0
Points
0
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
 

Attachments

  • FixErrordisplay.xlsx
    13.1 KB · Views: 15

Sixthsense

New member
Joined
Nov 6, 2012
Messages
26
Reaction score
0
Points
0
Location
India
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
 

jos

New member
Joined
Feb 28, 2014
Messages
7
Reaction score
0
Points
0
@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?
 

jos

New member
Joined
Feb 28, 2014
Messages
7
Reaction score
0
Points
0
sorted! see attachment
thank you for your contributions....
 

Attachments

  • FixErrordisplay_solution.xlsx
    13.6 KB · Views: 12

Sixthsense

New member
Joined
Nov 6, 2012
Messages
26
Reaction score
0
Points
0
Location
India
You can use Sum(), Product() which will handle the Text data nicely :)
 

jos

New member
Joined
Feb 28, 2014
Messages
7
Reaction score
0
Points
0
I didnt know this. I will experiment...
thank you
 
Top