Microsoft Office 2007 home and student

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)))/7I 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.

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

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!

You're welcome, and thanks for the feedback.

Regards

Humar

Ask Your Question

Weekly Poll