Formula Programming

[Guest]

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)

 

[Guest]

Try:-

=IF(AND(C6>=1,C6<=4),C45+C46,IF(AND(C6>=5,C6<=8),C45+(C46*2),IF(AND(C6>=9,C6<=12),C45+(C46*3),"Out of range")))

Mike

 

[Bob Phillips]

=IF(AND(C6>=1,C6<=4),C45+C46,IF(AND(C6>4,C6<=8),C45+C46*2,IF(AND(C6>8,C6<=12),C45+C46*3,"")))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

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<13,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

 

[Guest]

Many thanks Rick, this is a formula which will be whole numbers only

Regards

Craig

 

[Guest]

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]

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)

 

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Guest]

Hi rick

Many thanks for your reply
The original is correct i have double checked it

Regards

Craig