| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Programming
I want to programme so that if C6 is between 1 and 4 then the answer is Sum
of C45 and C46, if C6 is between 5 and 8 then the answer is sum of C45 and C46(X2), if the C6 is between 9 and 12 then the answer is sum of C45 and C46(X3) |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Programming
Try:-
=IF(AND(C6=1,C6<=4),C45+C46,IF(AND(C6=5,C6<=8),C 45+(C46*2),IF(AND(C6=9,C6<=12),C45+(C46*3),"Out of range"))) Mike "Foghat" wrote: I want to programme so that if C6 is between 1 and 4 then the answer is Sum of C45 and C46, if C6 is between 5 and 8 then the answer is sum of C45 and C46(X2), if the C6 is between 9 and 12 then the answer is sum of C45 and C46(X3) |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Programming
=IF(AND(C6=1,C6<=4),C45+C46,IF(AND(C64,C6<=8),C4 5+C46*2,IF(AND(C68,C6<=12),C45+C46*3,"")))
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Foghat" wrote in message ... I want to programme so that if C6 is between 1 and 4 then the answer is Sum of C45 and C46, if C6 is between 5 and 8 then the answer is sum of C45 and C46(X2), if the C6 is between 9 and 12 then the answer is sum of C45 and C46(X3) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Programming
I want to programme so that if C6 is between 1 and 4 then the answer is Sum
of C45 and C46, if C6 is between 5 and 8 then the answer is sum of C45 and C46(X2), if the C6 is between 9 and 12 then the answer is sum of C45 and C46(X3) Assuming C6 will only contain integer values... =IF(C6<1,"Out of range",IF(C6<5,C45+C46,IF(C6<9,C45+(C46*2),IF(C6<1 3,C45+(C46*3),"Out of range")))) If C6 can contain floating point numbers, then you have some holes in your original specification and need to clarify what happens between 4 and 5 as well as 8 and 9. Rick |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Programming
Many thanks Rick, this is a formula which will be whole numbers only
Regards Craig "Rick Rothstein (MVP - VB)" wrote: I want to programme so that if C6 is between 1 and 4 then the answer is Sum of C45 and C46, if C6 is between 5 and 8 then the answer is sum of C45 and C46(X2), if the C6 is between 9 and 12 then the answer is sum of C45 and C46(X3) Assuming C6 will only contain integer values... =IF(C6<1,"Out of range",IF(C6<5,C45+C46,IF(C6<9,C45+(C46*2),IF(C6<1 3,C45+(C46*3),"Out of range")))) If C6 can contain floating point numbers, then you have some holes in your original specification and need to clarify what happens between 4 and 5 as well as 8 and 9. Rick |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Programming
Many thanks Bob that really helped
I have one other problem which should enable me to complete this spreadsheet if you could help Here goes If C18 is between 1 and 4 and C6 is between 1 and 4 then the answer is C48, If C18 is between 5 and 8 and C6 is between 1 and 4 then the answer is C48x2 if C18 is between 9 and 12 and C6 is between 1 and 4 then the answer is C48 X 3 +C45 there are alot of different variables in this spreadsheet so I dont know if this is the right way to be doing it or is there another way which it can be done Many thanks in advance for everyones help Craig "Bob Phillips" wrote: =IF(AND(C6=1,C6<=4),C45+C46,IF(AND(C64,C6<=8),C4 5+C46*2,IF(AND(C68,C6<=12),C45+C46*3,""))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Foghat" wrote in message ... I want to programme so that if C6 is between 1 and 4 then the answer is Sum of C45 and C46, if C6 is between 5 and 8 then the answer is sum of C45 and C46(X2), if the C6 is between 9 and 12 then the answer is sum of C45 and C46(X3) |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Programming
what if C6 is not between 1 and 4 in each instance of C18?
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Foghat" wrote in message ... Many thanks Bob that really helped I have one other problem which should enable me to complete this spreadsheet if you could help Here goes If C18 is between 1 and 4 and C6 is between 1 and 4 then the answer is C48, If C18 is between 5 and 8 and C6 is between 1 and 4 then the answer is C48x2 if C18 is between 9 and 12 and C6 is between 1 and 4 then the answer is C48 X 3 +C45 there are alot of different variables in this spreadsheet so I dont know if this is the right way to be doing it or is there another way which it can be done Many thanks in advance for everyones help Craig "Bob Phillips" wrote: =IF(AND(C6=1,C6<=4),C45+C46,IF(AND(C64,C6<=8),C4 5+C46*2,IF(AND(C68,C6<=12),C45+C46*3,""))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Foghat" wrote in message ... I want to programme so that if C6 is between 1 and 4 then the answer is Sum of C45 and C46, if C6 is between 5 and 8 then the answer is sum of C45 and C46(X2), if the C6 is between 9 and 12 then the answer is sum of C45 and C46(X3) |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Programming
It always will have a number between 1 and 12 but was going to see how i got
on with the answer and then expand it if i could (didn't want to be taking the mickey and seem as if you were doing all the work for me) Many thanks CRaig "Foghat" wrote: Many thanks Bob that really helped I have one other problem which should enable me to complete this spreadsheet if you could help Here goes If C18 is between 1 and 4 and C6 is between 1 and 4 then the answer is C48, If C18 is between 5 and 8 and C6 is between 1 and 4 then the answer is C48x2 if C18 is between 9 and 12 and C6 is between 1 and 4 then the answer is C48 X 3 +C45 there are alot of different variables in this spreadsheet so I dont know if this is the right way to be doing it or is there another way which it can be done Many thanks in advance for everyones help Craig "Bob Phillips" wrote: =IF(AND(C6=1,C6<=4),C45+C46,IF(AND(C64,C6<=8),C4 5+C46*2,IF(AND(C68,C6<=12),C45+C46*3,""))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Foghat" wrote in message ... I want to programme so that if C6 is between 1 and 4 then the answer is Sum of C45 and C46, if C6 is between 5 and 8 then the answer is sum of C45 and C46(X2), if the C6 is between 9 and 12 then the answer is sum of C45 and C46(X3) |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Programming
We are use to that <G
=IF(AND(C6=1,C6<=4),IF(C18<1,"oops!",IF(C18<5,C48 ,IF(C18<9,C48*2,IF(C18<13,C48*3,"oops again!")))),"") -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Foghat" wrote in message ... It always will have a number between 1 and 12 but was going to see how i got on with the answer and then expand it if i could (didn't want to be taking the mickey and seem as if you were doing all the work for me) Many thanks CRaig "Foghat" wrote: Many thanks Bob that really helped I have one other problem which should enable me to complete this spreadsheet if you could help Here goes If C18 is between 1 and 4 and C6 is between 1 and 4 then the answer is C48, If C18 is between 5 and 8 and C6 is between 1 and 4 then the answer is C48x2 if C18 is between 9 and 12 and C6 is between 1 and 4 then the answer is C48 X 3 +C45 there are alot of different variables in this spreadsheet so I dont know if this is the right way to be doing it or is there another way which it can be done Many thanks in advance for everyones help Craig "Bob Phillips" wrote: =IF(AND(C6=1,C6<=4),C45+C46,IF(AND(C64,C6<=8),C4 5+C46*2,IF(AND(C68,C6<=12),C45+C46*3,""))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Foghat" wrote in message ... I want to programme so that if C6 is between 1 and 4 then the answer is Sum of C45 and C46, if C6 is between 5 and 8 then the answer is sum of C45 and C46(X2), if the C6 is between 9 and 12 then the answer is sum of C45 and C46(X3) |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Programming
Hi Bob
Many thanks for your help so far, i have one last query (I think it will be the last anyway as I get the general idea now) Here goes If C18 is between 1 and 4 and C6 is between 1 and 4 then the answer is C48, If C18 is between 5 and 8 and C6 is between 1 and 4 then the answer is C48x2 if C18 is between 9 and 12 and C6 is between 1 and 4 then the answer is C48 X 3 +C45 This is what you did last time) then i want to add If C18 is between 1 and 4 and C6 is between 5 and 8 then the answer isC48 If C18 is between 5 and 8 and C6 is between 5 and 8 then the answer is C48*2+C45 If C18 is between 9 and 12 and C6 is between 5 and 8 then the answer is C48*3 + C45 Many thanks Craig "Bob Phillips" wrote: We are use to that <G =IF(AND(C6=1,C6<=4),IF(C18<1,"oops!",IF(C18<5,C48 ,IF(C18<9,C48*2,IF(C18<13,C48*3,"oops again!")))),"") -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Foghat" wrote in message ... It always will have a number between 1 and 12 but was going to see how i got on with the answer and then expand it if i could (didn't want to be taking the mickey and seem as if you were doing all the work for me) Many thanks CRaig "Foghat" wrote: Many thanks Bob that really helped I have one other problem which should enable me to complete this spreadsheet if you could help Here goes If C18 is between 1 and 4 and C6 is between 1 and 4 then the answer is C48, If C18 is between 5 and 8 and C6 is between 1 and 4 then the answer is C48x2 if C18 is between 9 and 12 and C6 is between 1 and 4 then the answer is C48 X 3 +C45 there are alot of different variables in this spreadsheet so I dont know if this is the right way to be doing it or is there another way which it can be done Many thanks in advance for everyones help Craig "Bob Phillips" wrote: =IF(AND(C6=1,C6<=4),C45+C46,IF(AND(C64,C6<=8),C4 5+C46*2,IF(AND(C68,C6<=12),C45+C46*3,""))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Foghat" wrote in message ... I want to programme so that if C6 is between 1 and 4 then the answer is Sum of C45 and C46, if C6 is between 5 and 8 then the answer is sum of C45 and C46(X2), if the C6 is between 9 and 12 then the answer is sum of C45 and C46(X3) |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Programming
If C18 is between 1 and 4 and C6 is between 1 and 4 then the answer is
C48, If C18 is between 5 and 8 and C6 is between 1 and 4 then the answer is C48x2 Just checking to make sure you don't have a typo in the above "answer" condition... is it correct as typed or was it supposed to have been C48x2+C45? I ask because first and third answers for C6 between 1 and 4 as well as for C6 between 5 and 8 are the same AND the only difference in the answer between these two groups for the second C6 condition is in the addition of C45, which looked suspicious to me, so I thought I would check. Rick if C18 is between 9 and 12 and C6 is between 1 and 4 then the answer is C48 X 3 +C45 This is what you did last time) then i want to add If C18 is between 1 and 4 and C6 is between 5 and 8 then the answer isC48 If C18 is between 5 and 8 and C6 is between 5 and 8 then the answer is C48*2+C45 If C18 is between 9 and 12 and C6 is between 5 and 8 then the answer is C48*3 + C45 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Programming
Hi rick
Many thanks for your reply The original is correct i have double checked it Regards Craig "Rick Rothstein (MVP - VB)" wrote: If C18 is between 1 and 4 and C6 is between 1 and 4 then the answer is C48, If C18 is between 5 and 8 and C6 is between 1 and 4 then the answer is C48x2 Just checking to make sure you don't have a typo in the above "answer" condition... is it correct as typed or was it supposed to have been C48x2+C45? I ask because first and third answers for C6 between 1 and 4 as well as for C6 between 5 and 8 are the same AND the only difference in the answer between these two groups for the second C6 condition is in the addition of C45, which looked suspicious to me, so I thought I would check. Rick if C18 is between 9 and 12 and C6 is between 1 and 4 then the answer is C48 X 3 +C45 This is what you did last time) then i want to add If C18 is between 1 and 4 and C6 is between 5 and 8 then the answer isC48 If C18 is between 5 and 8 and C6 is between 5 and 8 then the answer is C48*2+C45 If C18 is between 9 and 12 and C6 is between 5 and 8 then the answer is C48*3 + C45 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel - Formula/Programming | Excel Worksheet Functions | |||
| Excel Formula / Programming | Excel Discussion (Misc queries) | |||
| Programming a simple formula | New Users to Excel | |||
| Programming A formula | Excel Programming | |||
| Programming A formula | Excel Programming | |||
Linear Mode
