#div/0!

**lornaerland** · 09-16-2014, 12:11 PM

I need help adding to my existing ratio formula the formula to get rid of the divided by 0 error.Custom Ratio Sheet.xlsx

**Richard Buttrey** · 09-16-2014, 12:16 PM

Hi,
Wrap it in an =IFERROR() function. e.g. re N2

Formula:

Please Login or Register  to view this content.

**lornaerland** · 09-16-2014, 12:41 PM

Ok that takes the error away but if there is a number in column F (19) and a 0 in column E shouldn't the ratio read 19:0? I am trying to show how may trial lenses are used per 6 PK ordered.

**Richard Buttrey** · 09-16-2014, 01:02 PM

Originally Posted by lornaerland

Ok that takes the error away but if there is a number in column F (19) and a 0 in column E shouldn't the ratio read 19:0? I am trying to show how may trial lenses are used per 6 PK ordered.

You originally asked about the Ratio column (there are two) and I gave you an answer that was relevant to column N.

Are you now asking about column G? If so then perhaps in G2

Formula:

Please Login or Register  to view this content.

**ben_hensel** · 09-16-2014, 01:04 PM

Originally Posted by lornaerland

...a 0 in column E shouldn't the ratio read 19:0?

No, it shouldn't.

Given E2 =0,
(E2/E2) isn't 0.

**lornaerland** · 09-16-2014, 01:28 PM

I am still trying to fix column G so maybe I need to start over. Column E is 6 pack boxes, column F is Trial lenses used. The ratio I want is how many trial lenses were use per 6 pack box? So if 5 boxes were ordered and 10 trials the ratio should be 2:1 correct? But what if 0 boxes have been ordered but 19 trials. What should the ratio look like 19:0? Can I modify this formula to reach my desired output or is my desired output incorrect?

**lornaerland** · 09-16-2014, 01:33 PM

Ok I took Richard Buttrey's formula =IFERROR(ROUND((F2/E2),1)&":"&(E2/E2),F2&":1") and changed the end to F2&":0") and that is giving me the desired out come. Thank you for your help!!!