"Between" in an IF clause
 

I have the following formula which works OK:

=IF(ISBLANK(AJ2),"",IF(H2<40,'Jobs (2)'!F2*0.682,IF(H2130,'Jobs
(2)'!F2*0.781,"0")))

But I need to add another IF clause to account for values in H2 which lie
between 40 and 130. How do I do that - I just can't work it out?

One way:

=IF(ISBLANK(AJ2),"",'Jobs (2)'!F2*IF(H2<40,0.682,IF(H2130,0.781,X)))

where X is your new value.





In article ,
"gavin" wrote:

I have the following formula which works OK:

=IF(ISBLANK(AJ2),"",IF(H2<40,'Jobs (2)'!F2*0.682,IF(H2130,'Jobs
(2)'!F2*0.781,"0")))

But I need to add another IF clause to account for values in H2 which lie
between 40 and 130. How do I do that - I just can't work it out?

You have already got it.
not blank, not smaller than 40 and not larger than130 ...........
i.e. in stead of "0" at the end , enter what ever factor you need.

Regards
Greetings from New Zealand
Bill K

"gavin" wrote in message
...
I have the following formula which works OK:

=IF(ISBLANK(AJ2),"",IF(H2<40,'Jobs (2)'!F2*0.682,IF(H2130,'Jobs
(2)'!F2*0.781,"0")))

But I need to add another IF clause to account for values in H2 which lie
between 40 and 130. How do I do that - I just can't work it out?

I believe all your needs can be met by following the procedure described in
http://www.mcgimpsey.com/excel/variablerate.html

This excellent method has solved similar problems for me.

Dan

"gavin" wrote in message
...
I have the following formula which works OK:

=IF(ISBLANK(AJ2),"",IF(H2<40,'Jobs (2)'!F2*0.682,IF(H2130,'Jobs
(2)'!F2*0.781,"0")))

But I need to add another IF clause to account for values in H2 which lie
between 40 and 130. How do I do that - I just can't work it out?

Doh! Why didn't I think of that??? That's Bill - much appreciated.


Regards,



Gavin





"Bill Kuunders" wrote in message
...
You have already got it.
not blank, not smaller than 40 and not larger than130 ...........
i.e. in stead of "0" at the end , enter what ever factor you need.

Regards
Greetings from New Zealand
Bill K

"gavin" wrote in message
...
I have the following formula which works OK:

=IF(ISBLANK(AJ2),"",IF(H2<40,'Jobs (2)'!F2*0.682,IF(H2130,'Jobs
(2)'!F2*0.781,"0")))

But I need to add another IF clause to account for values in H2 which
lie
between 40 and 130. How do I do that - I just can't work it out?

You're welcom.
Thanks for the feedback.
Must say though that JE Mc's answer is the same.
Regards

--
Greetings from New Zealand
Bill K

"gavin" wrote in message
...
Doh! Why didn't I think of that??? That's Bill - much appreciated.


Regards,



Gavin





"Bill Kuunders" wrote in message
...
You have already got it.
not blank, not smaller than 40 and not larger than130 ...........
i.e. in stead of "0" at the end , enter what ever factor you need.

Regards
Greetings from New Zealand
Bill K

"gavin" wrote in message
...
I have the following formula which works OK:

=IF(ISBLANK(AJ2),"",IF(H2<40,'Jobs (2)'!F2*0.682,IF(H2130,'Jobs
(2)'!F2*0.781,"0")))

But I need to add another IF clause to account for values in H2 which
lie
between 40 and 130. How do I do that - I just can't work it out?