Greater than AND less than

Microsoft Office 2007 home and student
June 24, 2010 at 13:33:20
Specs: Windows Vista
Currently, I have the following formula to calculate the number of weeks based on dates:

=IF(IF($E$2<L16,$E$2-K16,L16-K16)/7<=1,1,IF($E$2<L16,$E$2-K16,L16-K16)/7)

I was originally having problems because I did not want the cell to equal zero. Now I need it to do just that.

If $E$2<L16,$E$2-K16,L16-K16)/7 is between 0.2 and 1, I want it to equal 1. If it is less than 0.2 I want it to equal 0. If it is greater than one, I want it to show the answer to the formula ($E$2<L16,$E$2-K16,L16-K16)/7).

Can someone help me? I don't know how to put this into the formula. <1 and >0.2?



See More: Greater than AND less than

Report •

#1
June 24, 2010 at 21:53:13
There are a couple of problems with your question.

1 - I want it to show the answer to the formula ($E$2<L16,$E$2-K16,L16-K16)/7).

($E$2<L16,$E$2-K16,L16-K16)/7 is not a formula. Without the IF in front of it, it's just a text string.

2 - You've told us what you want if the value is between 2 numbers, you told us what you want if the value is less than one of the numbers and you've told us what you want if the value is greater than the other number.

What you haven't told us is what you want if the value equals either of those 2 numbers.

I'll assume you meant "less than or equal to" and "greater than or equal to".

I'm not going to claim that I understand what you are trying to do, but based on your question, I think this is what you want.

=IF(AND(IF(E2<L16,E2-K16,L16-K16)/7>=0.2,IF(E2<L16,E2-K16,L16-K16)/7<=1),1,IF(IF(E2<L16,E2-K16,L16-K16)/7<=0.2,0,IF(E2<L16,E2-K16,L16-K16)/7))


Report •

#2
June 25, 2010 at 12:41:47
Amazing! It works great!

Thank you, thank you, thank you!

Have a great weekend!


Report •

#3
June 25, 2010 at 12:53:07
Glad I could help.

In the future, one "Thank You" will suffice. ;-)


Report •
Related Solutions


Ask Question