Calculating Ratios with Zeros in some cells

Hi all,

I am calculating ratios to show as 1:0 or 3:2 etc using this formula: =IFERROR(CONCATENATE((K2/GCD(K2,N2)),":",((N2/GCD(K2,N2)))),"Nil")... I have a lot of columns that have zeros in them and the formula i am using doesn't seem to work for those...

Example

A B
3 6 should equal: 1:2 works OK
2 0 should equal: 2:0 my current result: 1:0
3 0 should equal: 3:0 my current result: 1:0
0 1 should equal: 0:1 works OK
0 0 should equal: Nil works OK
4 1 should equal: 4:1 works OK

Anything with a zero in the first column comes up as 1:0

Any help? THANK YOU!

Hi,

Try this formula instead:

Formula:

Please Login or Register  to view this content.

Hope this helps

Thanks for your suggestion, unfortunately that doesn't work.. it now only produces "Nil" for everything. Unless i am doing it wrong?

It worked for me. OK let me use your cell references from your formula rather than from your example:

Formula:

Please Login or Register  to view this content.

If this doesn't work, please upload your workbook

Sorry, it does work! I just have 24000 formulas so it took a while to process... THANK YOU for your help!!!

No problems

Please don't forget to mark this thread as solved (Thread Tools --> Mark As Solved), and please click on the * next to my post to say thanks