Ratios without #DIV/0! 2

[colinrharris]

First of all I should say that I am an Excel novice and I already had to look up the formula for displaying a result as a ratio.
I am calculating the ratio of the values in columns D and E to appear in column G to 1 decimal place. The formula I have found and adapted is:
=IF(E3>D3, TEXT(E3/D3, "0.0\:1"), TEXT(D3/E3, "1\:0.0"))

This works, but when one or both of the values is zero (or null), column G shows #DIV/0!

I know it is possible to suppress the error message with ISERROR but for the purpose of this exercise, I would rather treat any 0 in columns D or E as if it was a 1
So if column D was 100 and E was 600, the result will be 6.0:1
And if column D was 0 and E was 600, the result will be 600:1
Can anyone show me how to adapt the formula to achieve this?

Thanks

 

[SkipVought]

hi,

I would rather treat any 0 in columns D or E as if it was a 1

[tt]
=IF(E3>D3, TEXT(if(E3=0,1,E3)/if(D3=0,1,D3), "0.0\:1"), TEXT(if(D3=0,1,D3)/if(E3=0,1,E3), "1\:0.0"))[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[colinrharris]

Perfect. Thank you.

 

[mscallisto]

Hey that was a good post Skip, have a star.

Sam