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

Do you think Amazon can bring Internet access to less-developed regions?

Discuss in The Lounge

Poll History