Hi,

There is probably a very simple solution to this but I'll ask anyway!

I'd like a simple percentage formula that takes into account all
eventualities ie zero amounts, negative and positive amounts and doesn't
return a DIV0#.

The data looks something like this
A1 - 0
B1 - (255)

A2 - (255)
B2 - (111)

A3 - 155
B3 - 75

etc

I've started something that has become a string of IF and ANDs, there must
be something easier!.

Any help gratefully appreciated.

Hi David

Does this achieve what you are looking for
=IF(A1=0,0,(B1-A1)/A1)*SIGN(A1)



--
Regards

Roger Govier


"David" wrote in message
...
Hi,

There is probably a very simple solution to this but I'll ask anyway!

I'd like a simple percentage formula that takes into account all
eventualities ie zero amounts, negative and positive amounts and
doesn't
return a DIV0#.

The data looks something like this
A1 - 0
B1 - (255)

A2 - (255)
B2 - (111)

A3 - 155
B3 - 75

etc

I've started something that has become a string of IF and ANDs, there
must
be something easier!.

Any help gratefully appreciated.

If cell B1 contains the divisor I would simply do the following if

=if(B1 = 0,100,"enter your normal formula here")

If the divisor is 0, then the percenttage is very close to 100%.

"David" wrote:

Hi,

There is probably a very simple solution to this but I'll ask anyway!

I'd like a simple percentage formula that takes into account all
eventualities ie zero amounts, negative and positive amounts and doesn't
return a DIV0#.

The data looks something like this
A1 - 0
B1 - (255)

A2 - (255)
B2 - (111)

A3 - 155
B3 - 75

etc

I've started something that has become a string of IF and ANDs, there must
be something easier!.

Any help gratefully appreciated.

Roger was short changed for coffee this morning and dropped a few
parentheses

=IF(A1=0,0,((B1-A1)/A1)*SIGN(A1))

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Roger Govier" wrote in message
...
Hi David

Does this achieve what you are looking for
=IF(A1=0,0,(B1-A1)/A1)*SIGN(A1)



--
Regards

Roger Govier


"David" wrote in message
...
Hi,

There is probably a very simple solution to this but I'll ask anyway!

I'd like a simple percentage formula that takes into account all
eventualities ie zero amounts, negative and positive amounts and doesn't
return a DIV0#.

The data looks something like this
A1 - 0
B1 - (255)

A2 - (255)
B2 - (111)

A3 - 155
B3 - 75

etc

I've started something that has become a string of IF and ANDs, there
must
be something easier!.

Any help gratefully appreciated.

Hi Bernard

Definitely right about the shortage of caffeine this morning.
However, I don't think the extra parentheses makes any difference.
I had used "" as the return to 0 value in A1, and that gave me an error
because of multiplying by SIGN, but changing it to 0 resolved the
problem.

Your extra parentheses do allow the use of Null (if required) rather
than 0 as in
=IF(A1=0,"",((B1-A1))*SIGN(A1))

--
Regards

Roger Govier


"Bernard Liengme" wrote in message
...
Roger was short changed for coffee this morning and dropped a few
parentheses

=IF(A1=0,0,((B1-A1)/A1)*SIGN(A1))

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Roger Govier" wrote in message
...
Hi David

Does this achieve what you are looking for
=IF(A1=0,0,(B1-A1)/A1)*SIGN(A1)



--
Regards

Roger Govier


"David" wrote in message
...
Hi,

There is probably a very simple solution to this but I'll ask
anyway!

I'd like a simple percentage formula that takes into account all
eventualities ie zero amounts, negative and positive amounts and
doesn't
return a DIV0#.

The data looks something like this
A1 - 0
B1 - (255)

A2 - (255)
B2 - (111)

A3 - 155
B3 - 75

etc

I've started something that has become a string of IF and ANDs,
there must
be something easier!.

Any help gratefully appreciated.

=IF(A1=0,"",((B1-A1))*SIGN(A1))

should of course be
=IF(A1=0,"",((B1-A1)/A1)*SIGN(A1))

(hit delete accidentally when the spellchecker suggested repetition of
A1)
Now where's that coffee percolator?<bg

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
Hi Bernard

Definitely right about the shortage of caffeine this morning.
However, I don't think the extra parentheses makes any difference.
I had used "" as the return to 0 value in A1, and that gave me an
error because of multiplying by SIGN, but changing it to 0 resolved
the problem.

Your extra parentheses do allow the use of Null (if required) rather
than 0 as in
=IF(A1=0,"",((B1-A1))*SIGN(A1))

--
Regards

Roger Govier


"Bernard Liengme" wrote in message
...
Roger was short changed for coffee this morning and dropped a few
parentheses

=IF(A1=0,0,((B1-A1)/A1)*SIGN(A1))

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Roger Govier" wrote in message
...
Hi David

Does this achieve what you are looking for
=IF(A1=0,0,(B1-A1)/A1)*SIGN(A1)



--
Regards

Roger Govier


"David" wrote in message
...
Hi,

There is probably a very simple solution to this but I'll ask
anyway!

I'd like a simple percentage formula that takes into account all
eventualities ie zero amounts, negative and positive amounts and
doesn't
return a DIV0#.

The data looks something like this
A1 - 0
B1 - (255)

A2 - (255)
B2 - (111)

A3 - 155
B3 - 75

etc

I've started something that has become a string of IF and ANDs,
there must
be something easier!.

Any help gratefully appreciated.

Thanks for you help R B and J. The problem is solved.

Enjoy your coffee!!!

David


"Roger Govier" wrote:

=IF(A1=0,"",((B1-A1))*SIGN(A1))

should of course be
=IF(A1=0,"",((B1-A1)/A1)*SIGN(A1))

(hit delete accidentally when the spellchecker suggested repetition of
A1)
Now where's that coffee percolator?<bg

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
Hi Bernard

Definitely right about the shortage of caffeine this morning.
However, I don't think the extra parentheses makes any difference.
I had used "" as the return to 0 value in A1, and that gave me an
error because of multiplying by SIGN, but changing it to 0 resolved
the problem.

Your extra parentheses do allow the use of Null (if required) rather
than 0 as in
=IF(A1=0,"",((B1-A1))*SIGN(A1))

--
Regards

Roger Govier


"Bernard Liengme" wrote in message
...
Roger was short changed for coffee this morning and dropped a few
parentheses

=IF(A1=0,0,((B1-A1)/A1)*SIGN(A1))

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Roger Govier" wrote in message
...
Hi David

Does this achieve what you are looking for
=IF(A1=0,0,(B1-A1)/A1)*SIGN(A1)



--
Regards

Roger Govier


"David" wrote in message
...
Hi,

There is probably a very simple solution to this but I'll ask
anyway!

I'd like a simple percentage formula that takes into account all
eventualities ie zero amounts, negative and positive amounts and
doesn't
return a DIV0#.

The data looks something like this
A1 - 0
B1 - (255)

A2 - (255)
B2 - (111)

A3 - 155
B3 - 75

etc

I've started something that has become a string of IF and ANDs,
there must
be something easier!.

Any help gratefully appreciated.