|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 1st, 2005, 10:37 PM
|
|||
|
|||
"Between" in an IF clause
I have the following formula which works OK:
=IF(ISBLANK(AJ2),"",IF(H240,'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? 
|
|
#2
May 1st, 2005, 10:43 PM
|
|||
|
|||
|
One way:
=IF(ISBLANK(AJ2),"",'Jobs (2)'!F2*IF(H240,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(H240,'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?
|
|
#3
May 1st, 2005, 10:47 PM
|
|||
|
|||
|
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(H240,'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?
|
|
#4
May 2nd, 2005, 02:21 AM
|
|||
|
|||
|
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(H240,'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?
|
|
#5
May 2nd, 2005, 01:14 PM
|
|||
|
|||
|
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(H240,'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?
|
|
#6
May 2nd, 2005, 09:27 PM
|
|||
|
|||
|
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(H240,'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?
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Extremely slow IN clause | Carl Rapson | Running & Setting Up Queries | 8 | April 20th, 2005 11:15 PM |
| like clause issue... | Brad Pears | Setting Up & Running Reports | 1 | January 10th, 2005 10:52 PM |
| can I not put a where clause in a Transform query? | Richard Hollenbeck | Running & Setting Up Queries | 4 | December 24th, 2004 01:33 PM |
| Syntax error in from clause | MikeC | Running & Setting Up Queries | 2 | October 2nd, 2004 04:02 AM |
Linear Mode
