Formula to equal 1 never 0

Microsoft Office 2007 home and student
June 22, 2010 at 10:27:15
Specs: Windows Vista
Hoping you can help with the this formula so I can have no zero result.

I have entered a formula:
=(IF($E$2<L9,($E$2-K9),(L9-K9)))/7

I do not want it to give me an answer of zero though. If the answer is zero, I want it to show as a one.

How would I adjust this formula for that?

I'd greatly appreciate any assistance with this.

Thanks.


See More: Formula to equal 1 never 0

Report •

#1
June 22, 2010 at 10:46:56
Hi,

As I don't know what your data is, I have to assume that either $E$2-K9 or L9-K9 could equal zero. Therefore I have used an IF() statement to assess the result of either, and return 1 for a zero result or the original result if not zero.

=IF(IF($E$2<L9,$E$2-K9,L9-K9)/7=0,1,IF($E$2<L9,$E$2-K9,L9-K9)/7)

I don't think that you need all the brackets - so I have removed some. Test it with both version to be sure that both formulas perform identically.

Regards


Report •

#2
June 22, 2010 at 10:53:54
Thank you for your help!

I tried your formula but because I had set the cell to show no decimals, I needed to adjust it slightly.

This one worked:
=IF(IF($E$2<L12,$E$2-K12,L12-K12)/7<=1,1,IF($E$2<L12,$E$2-K12,L12-K12)/7)

I wouldn't have figured it out without your help though.

Thank you so much!


Report •

#3
June 22, 2010 at 11:17:50
You're welcome,

and thanks for the feedback.

Regards

Humar


Report •
Related Solutions


Ask Question