Nesting an additional formula in an IF statement

[Karsea]

I have an existing calculation in a column, when I drag the formula down - I
rec'v a #DIV/O - I need to insert a "Serror" in my existing formula but
unsure how to complete this?
My formula: In M5
=IF((H5/E5)=0,0%,(H5/E5)-1)
My formula works great, but I want it to show a blank cell (or 0%) so I can
use the sum of M5:M66 in another cell to show an average =AVERAGEA(M5:M66)
without rec'v a #DIV/0 (error)
Many thanks!!

 

[Max]

This tweak should suffice
In M5: =IF(E5=0,0%,(H5/E5)-1)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik

 

[Pecoflyer]

I have an existing calculation in a column, when I drag the formula dow
- I
rec'v a #DIV/O - I need to insert a "Serror" in my existing formul
but
unsure how to complete this?
My formula: In M5
=IF((H5/E5)=0,0%,(H5/E5)-1)
My formula works great, but I want it to show a blank cell (or 0%) so
can
use the sum of M5:M66 in another cell to show an averag
=AVERAGEA(M5:M66)
without rec'v a #DIV/0 (error)
Many thanks!!

If you replace it with 0%, will not your average be wrong

--
Pecoflye

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows fil
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faste

 

[Karsea]

Yes a 0% would not accurately determine the average - it would have to be a
blank cell!

 

[Karsea]

Hi Max,

In M5 I used your calculation but it changes my M5 to -100% (where it should
actually be 0) - as H5 = 0.00 in this row

 

[Pecoflyer]

Hi Max,

In M5 I used your calculation but it changes my M5 to -100% (where i
should
actually be 0) - as H5 = 0.00 in this row

Just to be on the safe side, could you post some of your data?
Why (H5/E5)-

--
Pecoflye

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows fil
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faste

 

[Karsea]

Okay here is a sample of my data

D E F G H I J
K L M
#Buy $Buy Fee TL Cost $Sold Sold Fee Date TL Cost
Gain/Loss$ G/L %
5)1000 2.10 9.95 2,090.05 0 9.95 2090.05
0 0%
6)350 5.9500 9.95 2072.55 6.3500 9.95 2212.55 140.00 6.72%
7)100 19.9800 9.95 1988.05 19.6100 9.95 1951.05
(37.00) -1.85%

My formula: In M5
=IF((H5/E5)=0,0%,(H5/E5)-1)
My formula works great, (as there is no loss it should still show as an
asset - will be sold at a later date) but I want if I drag the calculation
down it shows a #DIV/O in the empty cells I need it to shoew a blank cell so
I can use the sum of M5:M66 in another cell to show an average %
=AVERAGEA(M5:M66)
without rec'v a #DIV/0 (error)
*I think I have to nest another formula in (ISERR) but unsure how to do that
within the existing formula
Thanks again

 

[joeu2004]

Perhaps:

=if(iserror(H5/E5), 0, H5/E5 - 1)


----- original posting -----

 

[Pecoflyer]

Okay here is a sample of my data

D E F G H I J
K L M
#Buy $Buy Fee TL Cost $Sold Sold Fee Date TL Cost
Gain/Loss$ G/L %
5)1000 2.10 9.95 2,090.05 0 9.95
2090.05
0 0%
6)350 5.9500 9.95 2072.55 6.3500 9.95 2212.55 140.00
6.72%
7)100 19.9800 9.95 1988.05 19.6100 9.95 1951.05
(37.00) -1.85%

My formula: In M5
=IF((H5/E5)=0,0%,(H5/E5)-1)
My formula works great, (as there is no loss it should still show a
an
asset - will be sold at a later date) but I want if I drag th
calculation
down it shows a #DIV/O in the empty cells I need it to shoew a blan
cell so
I can use the sum of M5:M66 in another cell to show an average %
=AVERAGEA(M5:M66)
without rec'v a #DIV/0 (error)
*I think I have to nest another formula in (ISERR) but unsure how to d
that
within the existing formula
Thanks again




The Code Cage Forums
(http://www.thecodecage.com/forumz/showthread.php?t=61412)

Would this help =IF((H5/E5)=0,"",(H5/E5)-1)

--
Pecoflye

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows fil
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faste

 

[Sean Timmons]

Well, I think the problem is so... the end of the forumla says (H5/e5)-1. If
H5 = 0, then that's 0%. then you are subtracting 100%. Thus -100%.

I think you want, =IF(E5=0,0%,(H5/E5))

 

[Karsea]

Hey,
I used your calculation but it turns M5 into -100% (it should show 0% or
blank) as the units were not sold at this time

 

[Karsea]

Hi,
I used your calculation but it shows 0% in M5 (which is fine) but now all my
calculations in M6, M7 . . etc now show 0%(when in fact there is a
calculation tobe formatted)

K

 

[Karsea]

Hi,
Your calculation works great (=IF((H5/E5)=0,"",(H5/E5)-1) BUT . . . when I
drag the new and improved formula down to the empty cells it still gives me a
#DIV/0!

THXS!