Quite a simple one I think

[grrrrr]

I need to show a ratio in one cell that links into another.

The ratio is used sales to new but in my simple formula is coming out as #VALUE! if there aren't sales of one type due to dividing by zero.

How do I get around this?

Thanks!

 

[VoG]

Try like this

=IF(B1="","",A1/B1)

 

[grrrrr]

I'm not too sure that will work.

I'll give more detail. There are 2 boxes for used sale totals and 2 boxes for new sales and they need 1.5:1 used sales to new.

So, the formula used:new needs to be say, ((B1+B2)/(A1+A2))

It's just silly maths getting in the way. If there are 2 used sales but no new sales they have clearly met the target but it is trying to divide by zero and is messing up.

 

[VoG]

Try

=IF(A1+A2=0,"",(B1+B2)/(A1+A2))

 

[grrrrr]

I think that is the best it is going to get.

Thank you very much for your help

My only concern is the ratio won't pull through when they have no new sales even though the target has been met...

 

[VoG]

Then maybe

=IF(OR(A1=0,A2=0),"",(B1+B2)/(A1+A2))

 

[grrrrr]

There needs to be a figure in there to meet my 'over 1.5:1' requirement in another formula.

As it's dividing by 0 is it a cell format jobby?

This is hurting my head. You can't divide by zero, so a formula can't be in place - surely that's right? I will just have to do it manually in those cases I think?

 

[VoG]

Would this work for you

=IF(OR(A1=0,A2=0),1,(B1+B2)/(A1+A2))

 

[grrrrr]

Yes and No.

I guess you could say something along the lines of

IF A1+A2=0 and B1+B2 = more than 1.5, then 1.5.

That worked in with the original formula?

 

[VoG]

Maybe

=IF(AND(A1+A2=0,B1+B2>=1.5),1.5,IF(AND(A1+A2=0,B1+B2<1.5),1,(B1+B2)/(A1+A2)))