| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get the IF function to work on more than one criterion?
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(EG82<3,IF(C8=1,ED8*Fees!D9,IF(C8=2,ED8*Fees!D1 9,IF(C8=3,ED8*Fees!D29,0)))) and IF(EG83<6,IF(C8=1,ED8*Fees!D10,IF(C8=2,ED8*Fees!D 20,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! :o) xx |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I get the IF function to work on more than one criterion?
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*(C 8=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 Replace @mailinator.com with @tiscali.co.uk "Stefania" wrote in message ... 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(EG82<3,IF(C8=1,ED8*Fees!D9,IF(C8=2,ED8*Fees!D1 9,IF(C8=3,ED8*Fees!D29,0)))) and IF(EG83<6,IF(C8=1,ED8*Fees!D10,IF(C8=2,ED8*Fees!D 20,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! :o) xx |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I get the IF function to work on more than one criterio
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! :o) 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*(C 8=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 Replace @mailinator.com with @tiscali.co.uk "Stefania" wrote in message ... 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(EG82<3,IF(C8=1,ED8*Fees!D9,IF(C8=2,ED8*Fees!D1 9,IF(C8=3,ED8*Fees!D29,0)))) and IF(EG83<6,IF(C8=1,ED8*Fees!D10,IF(C8=2,ED8*Fees!D 20,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! :o) xx |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I get the IF function to work on more than one criterion?
Sandy,
Thank you! You are a god! I don't think I EVER would have got there! -- Steffi ******** It''''s Thank You and Goodnight! :o) 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(EG82<3,IF(C8=1,ED8*Fees!D9,IF(C8=2,ED8*Fees!D1 9,IF(C8=3,ED8*Fees!D29,0)))) and IF(EG83<6,IF(C8=1,ED8*Fees!D10,IF(C8=2,ED8*Fees!D 20,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! :o) xx |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I get the IF function to work on more than one criterion?
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 Replace @mailinator.com with @tiscali.co.uk "Stefania" wrote in message ... Sandy, Thank you! You are a god! I don't think I EVER would have got there! -- Steffi ******** It''''s Thank You and Goodnight! :o) 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(EG82<3,IF(C8=1,ED8*Fees!D9,IF(C8=2,ED8*Fees!D1 9,IF(C8=3,ED8*Fees!D29,0)))) and IF(EG83<6,IF(C8=1,ED8*Fees!D10,IF(C8=2,ED8*Fees!D 20,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! :o) xx |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| DBSUM function but with function as criterion, not a range | Excel Worksheet Functions | |||
| SUMIF Criterion with NOT | Excel Worksheet Functions | |||
| Multiple Criterion in a SUMIF function | Excel Worksheet Functions | |||
| IF(<criterion across sheets,1,0)? | Excel Worksheet Functions | |||
| How do I put more than one criterion in a SUMIF function? | Excel Worksheet Functions | |||
Linear Mode
