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))
"Pecoflyer" wrote:
Karsea;223363 Wrote:
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'
('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: 'The Code Cage Forums - View Profile: Pecoflyer'
(http://www.thecodecage.com/forumz/me...pecoflyer.html)
View this thread: 'Nesting an additional formula in an IF statement -
The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...ad.php?t=61412)
Would this help =IF((H5/E5)=0,"",(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