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!
----------

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 Removed)
Replace @mailinator.com with @tiscali.co.uk


"Stefania" <(E-Mail Removed)> wrote in message
news:2707F56A-5633-4AF1-9C0C-(E-Mail Removed)...
> 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!
> ----------
>
> It's Thank You and Goodnight! ) xx
>

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


"Sandy Mann" wrote:

> 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 Removed)
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "Stefania" <(E-Mail Removed)> wrote in message
> news:2707F56A-5633-4AF1-9C0C-(E-Mail Removed)...
> > 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!
> > ----------
> >
> > It's Thank You and Goodnight! ) xx
> >
>
>
>
>

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 Removed)
Replace @mailinator.com with @tiscali.co.uk


"Stefania" <(E-Mail Removed)> wrote in message
news:4EF74176-3052-4910-A63A-(E-Mail Removed)...
> 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
>
>
> "Sandy Mann" wrote:
>
>> 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 Removed)
>> Replace @mailinator.com with @tiscali.co.uk
>>
>>
>> "Stefania" <(E-Mail Removed)> wrote in message
>> news:2707F56A-5633-4AF1-9C0C-(E-Mail Removed)...
>> > 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!
>> > ----------
>> >
>> > It's Thank You and Goodnight! ) xx
>> >
>>
>>
>>
>>
>

Sandy,

Thank you!
You are a god!

I don't think I EVER would have got there!
--
Steffi
********
It''''s Thank You and Goodnight! ) xx


"Stefania" wrote:

> 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!
> ----------
>
> It's Thank You and Goodnight! ) xx

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 Removed)
Replace @mailinator.com with @tiscali.co.uk


"Stefania" <(E-Mail Removed)> wrote in message
news:76E61E5A-F919-4809-959D-(E-Mail Removed)...
> Sandy,
>
> Thank you!
> You are a god!
>
> I don't think I EVER would have got there!
> --
> Steffi
> ********
> It''''s Thank You and Goodnight! ) xx
>
>
> "Stefania" wrote:
>
>> 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!
>> ----------
>>
>> It's Thank You and Goodnight! ) xx
>