Hello Gurus!

Here's the deal -- I'm trying to get a percentage value for column VP
as presented below:

July 2005
A B V VP

10 4,757 (4,747) -99.79%
0 32,730 (32,730) -100.00%
0 9,519 (9,519) -100.00%
0 4,757 (4,757) -100.00%
10 0 10
0 0 0
20 51,763 (51,743) -99.96%

Since columns A and B may often contain 0 as a value, I get a #DIV/0
error of I try to calculate a percentage (using =V/B).

As a workaround, I have tried "=IF(ISERROR(B4/C4=0),"",D4/C4)" where B4
is the cell in column A, C4 is the cell in column B and the percentage
of variance (VP) is the cell in column V divided by the cell in column
B.

The problem is that the ISERROR formula as I have written it will not
show a value for the variance where there is 0 in either column A or B,
even though one of the two columns may have a value other than 0.

Does anyone know how to get the variance percentage (VP) to get written
while still suppressing the error message?

Thanks for any help with this.


--
forumuser
------------------------------------------------------------------------
forumuser's Profile: http://www.excelforum.com/member.php...o&userid=26106
View this thread: http://www.excelforum.com/showthread...hreadid=394365

=IF(C4=0,"",D4/C4)


forumuser Wrote:
Hello Gurus!

Here's the deal -- I'm trying to get a percentage value for column VP
as presented below:

July 2005
A B V VP

10 4,757 (4,747) -99.79%
0 32,730 (32,730) -100.00%
0 9,519 (9,519) -100.00%
0 4,757 (4,757) -100.00%
10 0 10
0 0 0
20 51,763 (51,743) -99.96%

Since columns A and B may often contain 0 as a value, I get a #DIV/0
error of I try to calculate a percentage (using =V/B).

As a workaround, I have tried "=IF(ISERROR(B4/C4=0),"",D4/C4)" where B4
is the cell in column A, C4 is the cell in column B and the percentage
of variance (VP) is the cell in column V divided by the cell in column
B.

The problem is that the ISERROR formula as I have written it will not
show a value for the variance where there is 0 in either column A or B,
even though one of the two columns may have a value other than 0.

Does anyone know how to get the variance percentage (VP) to get written
while still suppressing the error message?

Thanks for any help with this.


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=394365

Thanks for the response, Morrigan. Unfortunately, not quite there --
here are the results (I changed the value in B6 to see what would
happen):

A****B****V****VP

10****4,757****(4,747)****-99.8%
0****32,730****(32,730)****-100.0%
0****9,519****(9,519)****-100.0%
0****4,757****(4,757)****-100.0%
10****0****10****
0****10****(10)****-100.0%

Any further ideas?


--
forumuser
------------------------------------------------------------------------
forumuser's Profile: http://www.excelforum.com/member.php...o&userid=26106
View this thread: http://www.excelforum.com/showthread...hreadid=394365

I have always admitted to not being overly bright, so would someone please
tell me what is Column V and VP?!?!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"forumuser" wrote
in message ...

Thanks for the response, Morrigan. Unfortunately, not quite there --
here are the results (I changed the value in B6 to see what would
happen):

A****B****V****VP

10****4,757****(4,747)****-99.8%
0****32,730****(32,730)****-100.0%
0****9,519****(9,519)****-100.0%
0****4,757****(4,757)****-100.0%
10****0****10****
0****10****(10)****-100.0%

Any further ideas?


--
forumuser
------------------------------------------------------------------------
forumuser's Profile:
http://www.excelforum.com/member.php...o&userid=26106
View this thread: http://www.excelforum.com/showthread...hreadid=394365

See if this is what you want. You will have to change the cell references to
match your setup. I used A, B, C and D. My assumptions; if A and B are both
0, the VP is zero as well and if B is zero, the VP is +100%. I get the
results below.

=IF(SUM(A3:B3)=0,0,IF(B3=0,1,C3/B3))


A B V VP

10 4757 -4747 -99.79%
0 32730 -32730 -100.00%
0 9519 -9519 -100.00%
0 4757 -4757 -100.00%
10 0 10 100.00%
0 0 0 0.00%
20 51763 -51743 -99.96%


Roy

--
(delete .nospam)




"forumuser" wrote:


Hello Gurus!

Here's the deal -- I'm trying to get a percentage value for column VP
as presented below:

July 2005
A B V VP

10 4,757 (4,747) -99.79%
0 32,730 (32,730) -100.00%
0 9,519 (9,519) -100.00%
0 4,757 (4,757) -100.00%
10 0 10
0 0 0
20 51,763 (51,743) -99.96%

Since columns A and B may often contain 0 as a value, I get a #DIV/0
error of I try to calculate a percentage (using =V/B).

As a workaround, I have tried "=IF(ISERROR(B4/C4=0),"",D4/C4)" where B4
is the cell in column A, C4 is the cell in column B and the percentage
of variance (VP) is the cell in column V divided by the cell in column
B.

The problem is that the ISERROR formula as I have written it will not
show a value for the variance where there is 0 in either column A or B,
even though one of the two columns may have a value other than 0.

Does anyone know how to get the variance percentage (VP) to get written
while still suppressing the error message?

Thanks for any help with this.


--
forumuser
------------------------------------------------------------------------
forumuser's Profile: http://www.excelforum.com/member.php...o&userid=26106
View this thread: http://www.excelforum.com/showthread...hreadid=394365

Why not keep it simple:

=IF(ISERROR(C2/B2)=TRUE," ",C2/B2)

Or am I missing something?


--
wmjenner


------------------------------------------------------------------------
wmjenner's Profile: http://www.excelforum.com/member.php...fo&userid=5282
View this thread: http://www.excelforum.com/showthread...hreadid=394365

Thank you, Roy, that'll do it perfectly. And thanks to everyone who
gave this problem some thought.