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
"Pecoflyer" wrote:
Karsea;223097 Wrote:
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
"Max" wrote:
This tweak should suffice
In M5: =IF(E5=0,0%,(H5/E5)-1)
--
Max
Singapore
'Free file hosting by Savefile.com'
(http://savefile.com/projects/236895)
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"Karsea" wrote:
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!!
Just to be on the safe side, could you post some of your data?
Why (H5/E5)-1
--
Pecoflyer
Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload -faster and better answers
*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=61412