Nesting an additional formula in an IF statement

K

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!!
 
P

Pecoflyer

Karsea;222380 said:
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
 
K

Karsea

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

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
 
P

Pecoflyer

Karsea;223097 said:
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
 
K

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
 
P

Pecoflyer

Karsea;223363 said:
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
 
S

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))
 
K

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
 
K

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
 
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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top