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

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

Karsea;222380 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!!

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


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

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

"Pecoflyer" wrote:


Karsea;222380 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!!

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


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

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

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

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

Perhaps:

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


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

On Feb 9, 7:35*pm, 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!!

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

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

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

"joeu2004" wrote:

Perhaps:

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


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

On Feb 9, 7:35 pm, 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!!

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

"Sean Timmons" wrote:

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

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!

"Sean Timmons" wrote:

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