How do I get the IF function to work on more than one criterion?

[Stefania]

Hi!

I'm writing a formula but I can't get my head around it.

The Formula below works:

=IF(EG8<2,IF(C8=1,ED8*Fees!D8,IF(C8=2,ED8*Fees!D18,IF(C8=3,ED8*Fees!D28,0))),0)

However I also need to add
IF(EG8>2<3,IF(C8=1,ED8*Fees!D9,IF(C8=2,ED8*Fees!D19,IF(C8=3,ED8*Fees!D29,0))))

and
IF(EG8>3<6,IF(C8=1,ED8*Fees!D10,IF(C8=2,ED8*Fees!D20,IF(C8=3,ED8*Fees!D30,0))))

Am I asking too much or can it be done???

Thanks in advance for ANY kind of response!

 

[Sandy Mann]

Not exactly what you would call elegant and it assumes that you mean EG8<2
or OR(EG8>=2,EG8<3) or EG8>=3 but try:

=IF(OR(EG8={1,2,3}),ED8*((((Fees!D8*(C8=1))+(Fees!D18*(C8=2))+(Fees!D28*(C8=3)))*(G8<2))+(((Fees!D9*(C8=1))+(Fees!D19*(C8=2))+(Fees!D29*(C8=3)))*(AND(G8>=2,G8<3)))+(((Fees!D10*(C8=1))+(Fees!D20*(C8=2))+(Fees!D30*(C8=3)))*(AND(G8>=3)))),0)

It works because there will only be one number returned out of:

(((Fees!D8*(C8=1))+(Fees!D18*(C8=2))+(Fees!D28*(C8=3)))*(G8<2))
or
(((Fees!D9*(C8=1))+(Fees!D19*(C8=2))+(Fees!D29*(C8=3)))*(AND(G8>=2,G8<3)))
or
(((Fees!D10*(C8=1))+(Fees!D20*(C8=2))+(Fees!D30*(C8=3)))*(AND(G8>=3)))

with the other two returning zeros and this number is then multplied by ED8

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Stefania]

Hi Sandy,

Thanks for your help! I wasn't going for elegant I was trying for
functional! ;o) Lol!

Let me try and explain a bit more.

In Cell C8 is a number either 1,2 or 3 - These refer to particular bandings
of costs.
Within the bands of costs are three different sets of figures dependant on a
child's age- whether they are 0-2, 2-3 or 3-5

Cell EG8 calculates the child's age

In Cell ED8 are the number of sessions the child has attended

I need the formula to multiply the cost of the session by the number of
sessions the child has attended by the cost of the band they are in dependant
on the child's age.


--
Steffi
********
It''s Thank You and Goodnight! ) xx

Not exactly what you would call elegant and it assumes that you mean EG8<2
or OR(EG8>=2,EG8<3) or EG8>=3 but try:

=IF(OR(EG8={1,2,3}),ED8*((((Fees!D8*(C8=1))+(Fees!D18*(C8=2))+(Fees!D28*(C8=3)))*(G8<2))+(((Fees!D9*(C8=1))+(Fees!D19*(C8=2))+(Fees!D29*(C8=3)))*(AND(G8>=2,G8<3)))+(((Fees!D10*(C8=1))+(Fees!D20*(C8=2))+(Fees!D30*(C8=3)))*(AND(G8>=3)))),0)

It works because there will only be one number returned out of:

(((Fees!D8*(C8=1))+(Fees!D18*(C8=2))+(Fees!D28*(C8=3)))*(G8<2))
or
(((Fees!D9*(C8=1))+(Fees!D19*(C8=2))+(Fees!D29*(C8=3)))*(AND(G8>=2,G8<3)))
or
(((Fees!D10*(C8=1))+(Fees!D20*(C8=2))+(Fees!D30*(C8=3)))*(AND(G8>=3)))

with the other two returning zeros and this number is then multplied by ED8

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

That simplifies it greatly.

Follow me through with this:

I created a lookup table in J1:M4 as follows:
J2: enter 0
J3: enter 2
J4: enter 3

K1:M1 enter 1, 2 & 3 respectively

K2: enter =Fees!D8
K3: enter =Fees!D18
K4: enter =Fees!D28

In L2:L4 enter =Fees!D9, =Fees!D19 & =Fees!D29 respectively and then finally
In M2:M4 enter =Fees!D10, =Fees!D20 & =Fees!D30 respectively.
(or enter the values that are in those cells directly into the table which
ever is easier)

You can then use the formula:

=IF(COUNT(ED8,EG8,C8)<3,"",ED8*VLOOKUP(EG8,J1:M4,MATCH(C8,K1:M1)+1))

Much more elegant.<g>

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Hi Sandy,

Thanks for your help! I wasn't going for elegant I was trying for
functional! ;o) Lol!

Let me try and explain a bit more.

In Cell C8 is a number either 1,2 or 3 - These refer to particular
bandings
of costs.
Within the bands of costs are three different sets of figures dependant on
a
child's age- whether they are 0-2, 2-3 or 3-5

Cell EG8 calculates the child's age

In Cell ED8 are the number of sessions the child has attended

I need the formula to multiply the cost of the session by the number of
sessions the child has attended by the cost of the band they are in
dependant
on the child's age.

 

[Stefania]

Sandy,

Thank you!
You are a god!

I don't think I EVER would have got there!

 

[Sandy Mann]

Just glad that you got it to work. Thanks got posting back.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Sandy,

Thank you!
You are a god!

I don't think I EVER would have got there!