Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function
Fee list per term
Code description Code E (student yr =1) E (student yr =2) E (student yr 2) O (student yr =1) O (student yr =2) O (student yr 2) Full-time F 1,263.33 1,363.33 1,316.67 3,180.00 3,820.00 3,820.00 Part-time P 631.67 681.67 658.33 1,590.00 1,910.00 1,910.00 Field-work L 626.67 681.67 658.33 626.67 681.67 658.33 Part-time field-work Z 313.33 341.67 330.00 313.33 341.67 330.00 Student name Student yr Mode Pattern Fee (maually calculated) Tom 1 O FFF 9,540.00 Susan 2 E PPP 2,045.00 Sam 6 O PFF 9,550.00 I need help to write a function that would calcuate the fee column according to the above table, the fee charged is based on their pattern of study, their mode (O= overseas, E=home) and the student yr. The pattern of study is made up of three terms, the student can study any combination, i.e. Three terms fulltime (FFF), 2 terms part-time and 1term fulltime (PFP, PPF etc.). I have manually calculated the fee for you to confirm whether the function is returning the correct answer. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function
I set up your table like this:
E1 E2 E3 O1 O2 O3 Full-time F 1263.33 1363.33 1316.67 3180.00 3820.00 3820.00 Part-time P 631.67 681.67 658.33 1590.00 1910.00 1910.00 Field-work L 626.67 681.67 658.33 626.67 681.67 658.33 PT field-work Z 313.33 341.67 330.00 313.33 341.67 330.00 so that it occupied cells A1:H5. It is important to use the same header row for my formula to work. With the Student Name header in row 10, I then put your example data in A11:D13, and then put this formula in E11: =ROUND(INDEX($C$2:$H$5,MATCH(LEFT(D11,1),$B$2:$B $5,0),MATCH(C11&IF(B112,3,B11),$C$1:$H$1,0))+INDE X($C$2:$H $5,MATCH(MID(D11,2,1),$B$2:$B$5,0),MATCH(C11&IF(B1 12,3,B11),$C$1:$H $1,0))+INDEX($C$2:$H$5,MATCH(RIGHT(D11,1),$B$2:$B $5,0),MATCH(C11&IF(B112,3,B11),$C$1:$H$1,0)),0) which was then copied down into E12:E13. It returned the same results as your manually-calculated values. It's a dear do going to university these days !! <bg Hope this helps. Pete On Apr 23, 10:17*am, nc wrote: Fee list per term * * * * * * * * * * * * * * * * * * * * * * * * * * * Code description * * * *Code * * E (student yr =1) * * *E (student yr =2) * * * E (student yr2) *O (student yr =1) * * * O (student yr =2) * * * O (student yr 2) Full-time * * * F * * * 1,263.33 * * * *1,363.33 * * * *1,316.67 * * * *3,180.00 * * * *3,820.00 * * * *3,820.00 Part-time * * * P * * * 631.67 *681.67 *658.33 *1,590.00 * * * *1,910.00 * * * *1,910.00 Field-work * * *L * * * 626.67 *681.67 *658.33 *626.67 *681.67 *658.33 Part-time field-work * *Z * * * 313.33 *341.67 *330.00 *313.33 *341.67 *330.00 Student name * *Student yr * * *Mode * *Pattern Fee (maually calculated) Tom * * 1 * * * O * * * FFF * * 9,540.00 Susan * 2 * * * E * * * PPP * * 2,045.00 Sam * * 6 * * * O * * * PFF * * 9,550.00 I need help to write a function that would calcuate the fee column according to the above table, the fee charged is based on their pattern of study, their mode (O= overseas, E=home) and the student yr. The pattern of study is made up of three terms, the student can study any combination, i.e. *Three terms fulltime (FFF), 2 terms part-time and 1term fulltime (PFP, PPF etc.). I have manually calculated the fee for you to confirm whether the function is returning the correct answer. Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function
Assuming the fee table is in sheet 1, A1:H5, and the results table data is
in sheet2 A1:E4, in Sheet2!E2, enter this formula and copy down =SUM(N(OFFSET(INDEX(Sheet1!$C$2:$H$5,0,MATCH(MIN($ B2,3)&$C2,{"1E","2E","3E","1O","2O","3O"},0)), MATCH(MID($D2,ROW(INDIRECT("1:"&LEN($D2))),1),Shee t1!$B$2:$B$5,0)-1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. Note that you cannot use a whole column in array formulae (prior to excel 2007), but must use an explicit range. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... Fee list per term Code description Code E (student yr =1) E (student yr =2) E (student yr 2) O (student yr =1) O (student yr =2) O (student yr 2) Full-time F 1,263.33 1,363.33 1,316.67 3,180.00 3,820.00 3,820.00 Part-time P 631.67 681.67 658.33 1,590.00 1,910.00 1,910.00 Field-work L 626.67 681.67 658.33 626.67 681.67 658.33 Part-time field-work Z 313.33 341.67 330.00 313.33 341.67 330.00 Student name Student yr Mode Pattern Fee (maually calculated) Tom 1 O FFF 9,540.00 Susan 2 E PPP 2,045.00 Sam 6 O PFF 9,550.00 I need help to write a function that would calcuate the fee column according to the above table, the fee charged is based on their pattern of study, their mode (O= overseas, E=home) and the student yr. The pattern of study is made up of three terms, the student can study any combination, i.e. Three terms fulltime (FFF), 2 terms part-time and 1term fulltime (PFP, PPF etc.). I have manually calculated the fee for you to confirm whether the function is returning the correct answer. Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function
Bob, Thanks for the solution.
What about if the fee is the same if the student year is 3 onwards (i.e. student yr2)? Can this function be used within an if statement? "Bob Phillips" wrote: Assuming the fee table is in sheet 1, A1:H5, and the results table data is in sheet2 A1:E4, in Sheet2!E2, enter this formula and copy down =SUM(N(OFFSET(INDEX(Sheet1!$C$2:$H$5,0,MATCH(MIN($ B2,3)&$C2,{"1E","2E","3E","1O","2O","3O"},0)), MATCH(MID($D2,ROW(INDIRECT("1:"&LEN($D2))),1),Shee t1!$B$2:$B$5,0)-1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. Note that you cannot use a whole column in array formulae (prior to excel 2007), but must use an explicit range. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... Fee list per term Code description Code E (student yr =1) E (student yr =2) E (student yr 2) O (student yr =1) O (student yr =2) O (student yr 2) Full-time F 1,263.33 1,363.33 1,316.67 3,180.00 3,820.00 3,820.00 Part-time P 631.67 681.67 658.33 1,590.00 1,910.00 1,910.00 Field-work L 626.67 681.67 658.33 626.67 681.67 658.33 Part-time field-work Z 313.33 341.67 330.00 313.33 341.67 330.00 Student name Student yr Mode Pattern Fee (maually calculated) Tom 1 O FFF 9,540.00 Susan 2 E PPP 2,045.00 Sam 6 O PFF 9,550.00 I need help to write a function that would calcuate the fee column according to the above table, the fee charged is based on their pattern of study, their mode (O= overseas, E=home) and the student yr. The pattern of study is made up of three terms, the student can study any combination, i.e. Three terms fulltime (FFF), 2 terms part-time and 1term fulltime (PFP, PPF etc.). I have manually calculated the fee for you to confirm whether the function is returning the correct answer. Thanks. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function
I cater for years 3 onwards all being year 3 fees as per your example with
the MIN($B2,3) statement -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... Bob, Thanks for the solution. What about if the fee is the same if the student year is 3 onwards (i.e. student yr2)? Can this function be used within an if statement? "Bob Phillips" wrote: Assuming the fee table is in sheet 1, A1:H5, and the results table data is in sheet2 A1:E4, in Sheet2!E2, enter this formula and copy down =SUM(N(OFFSET(INDEX(Sheet1!$C$2:$H$5,0,MATCH(MIN($ B2,3)&$C2,{"1E","2E","3E","1O","2O","3O"},0)), MATCH(MID($D2,ROW(INDIRECT("1:"&LEN($D2))),1),Shee t1!$B$2:$B$5,0)-1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. Note that you cannot use a whole column in array formulae (prior to excel 2007), but must use an explicit range. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... Fee list per term Code description Code E (student yr =1) E (student yr =2) E (student yr 2) O (student yr =1) O (student yr =2) O (student yr 2) Full-time F 1,263.33 1,363.33 1,316.67 3,180.00 3,820.00 3,820.00 Part-time P 631.67 681.67 658.33 1,590.00 1,910.00 1,910.00 Field-work L 626.67 681.67 658.33 626.67 681.67 658.33 Part-time field-work Z 313.33 341.67 330.00 313.33 341.67 330.00 Student name Student yr Mode Pattern Fee (maually calculated) Tom 1 O FFF 9,540.00 Susan 2 E PPP 2,045.00 Sam 6 O PFF 9,550.00 I need help to write a function that would calcuate the fee column according to the above table, the fee charged is based on their pattern of study, their mode (O= overseas, E=home) and the student yr. The pattern of study is made up of three terms, the student can study any combination, i.e. Three terms fulltime (FFF), 2 terms part-time and 1term fulltime (PFP, PPF etc.). I have manually calculated the fee for you to confirm whether the function is returning the correct answer. Thanks. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function
Bob, thanks.
Can you use an array formula within an IF function? "Bob Phillips" wrote: I cater for years 3 onwards all being year 3 fees as per your example with the MIN($B2,3) statement -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... Bob, Thanks for the solution. What about if the fee is the same if the student year is 3 onwards (i.e. student yr2)? Can this function be used within an if statement? "Bob Phillips" wrote: Assuming the fee table is in sheet 1, A1:H5, and the results table data is in sheet2 A1:E4, in Sheet2!E2, enter this formula and copy down =SUM(N(OFFSET(INDEX(Sheet1!$C$2:$H$5,0,MATCH(MIN($ B2,3)&$C2,{"1E","2E","3E","1O","2O","3O"},0)), MATCH(MID($D2,ROW(INDIRECT("1:"&LEN($D2))),1),Shee t1!$B$2:$B$5,0)-1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. Note that you cannot use a whole column in array formulae (prior to excel 2007), but must use an explicit range. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... Fee list per term Code description Code E (student yr =1) E (student yr =2) E (student yr 2) O (student yr =1) O (student yr =2) O (student yr 2) Full-time F 1,263.33 1,363.33 1,316.67 3,180.00 3,820.00 3,820.00 Part-time P 631.67 681.67 658.33 1,590.00 1,910.00 1,910.00 Field-work L 626.67 681.67 658.33 626.67 681.67 658.33 Part-time field-work Z 313.33 341.67 330.00 313.33 341.67 330.00 Student name Student yr Mode Pattern Fee (maually calculated) Tom 1 O FFF 9,540.00 Susan 2 E PPP 2,045.00 Sam 6 O PFF 9,550.00 I need help to write a function that would calcuate the fee column according to the above table, the fee charged is based on their pattern of study, their mode (O= overseas, E=home) and the student yr. The pattern of study is made up of three terms, the student can study any combination, i.e. Three terms fulltime (FFF), 2 terms part-time and 1term fulltime (PFP, PPF etc.). I have manually calculated the fee for you to confirm whether the function is returning the correct answer. Thanks. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function
Bob I like your solution, what about if the mode could be H and has the same
fee status as E. Thanks. "Bob Phillips" wrote: Assuming the fee table is in sheet 1, A1:H5, and the results table data is in sheet2 A1:E4, in Sheet2!E2, enter this formula and copy down =SUM(N(OFFSET(INDEX(Sheet1!$C$2:$H$5,0,MATCH(MIN($ B2,3)&$C2,{"1E","2E","3E","1O","2O","3O"},0)), MATCH(MID($D2,ROW(INDIRECT("1:"&LEN($D2))),1),Shee t1!$B$2:$B$5,0)-1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. Note that you cannot use a whole column in array formulae (prior to excel 2007), but must use an explicit range. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... Fee list per term Code description Code E (student yr =1) E (student yr =2) E (student yr 2) O (student yr =1) O (student yr =2) O (student yr 2) Full-time F 1,263.33 1,363.33 1,316.67 3,180.00 3,820.00 3,820.00 Part-time P 631.67 681.67 658.33 1,590.00 1,910.00 1,910.00 Field-work L 626.67 681.67 658.33 626.67 681.67 658.33 Part-time field-work Z 313.33 341.67 330.00 313.33 341.67 330.00 Student name Student yr Mode Pattern Fee (maually calculated) Tom 1 O FFF 9,540.00 Susan 2 E PPP 2,045.00 Sam 6 O PFF 9,550.00 I need help to write a function that would calcuate the fee column according to the above table, the fee charged is based on their pattern of study, their mode (O= overseas, E=home) and the student yr. The pattern of study is made up of three terms, the student can study any combination, i.e. Three terms fulltime (FFF), 2 terms part-time and 1term fulltime (PFP, PPF etc.). I have manually calculated the fee for you to confirm whether the function is returning the correct answer. Thanks. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function
Yes, indeed you can. It is a very common technique, such as
pseudo-AVERAGEIF =AVERAGE(IF(rng0,rng)) which only averages the values greater than 0. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... Bob, thanks. Can you use an array formula within an IF function? "Bob Phillips" wrote: I cater for years 3 onwards all being year 3 fees as per your example with the MIN($B2,3) statement -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... Bob, Thanks for the solution. What about if the fee is the same if the student year is 3 onwards (i.e. student yr2)? Can this function be used within an if statement? "Bob Phillips" wrote: Assuming the fee table is in sheet 1, A1:H5, and the results table data is in sheet2 A1:E4, in Sheet2!E2, enter this formula and copy down =SUM(N(OFFSET(INDEX(Sheet1!$C$2:$H$5,0,MATCH(MIN($ B2,3)&$C2,{"1E","2E","3E","1O","2O","3O"},0)), MATCH(MID($D2,ROW(INDIRECT("1:"&LEN($D2))),1),Shee t1!$B$2:$B$5,0)-1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. Note that you cannot use a whole column in array formulae (prior to excel 2007), but must use an explicit range. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... Fee list per term Code description Code E (student yr =1) E (student yr =2) E (student yr 2) O (student yr =1) O (student yr =2) O (student yr 2) Full-time F 1,263.33 1,363.33 1,316.67 3,180.00 3,820.00 3,820.00 Part-time P 631.67 681.67 658.33 1,590.00 1,910.00 1,910.00 Field-work L 626.67 681.67 658.33 626.67 681.67 658.33 Part-time field-work Z 313.33 341.67 330.00 313.33 341.67 330.00 Student name Student yr Mode Pattern Fee (maually calculated) Tom 1 O FFF 9,540.00 Susan 2 E PPP 2,045.00 Sam 6 O PFF 9,550.00 I need help to write a function that would calcuate the fee column according to the above table, the fee charged is based on their pattern of study, their mode (O= overseas, E=home) and the student yr. The pattern of study is made up of three terms, the student can study any combination, i.e. Three terms fulltime (FFF), 2 terms part-time and 1term fulltime (PFP, PPF etc.). I have manually calculated the fee for you to confirm whether the function is returning the correct answer. Thanks. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function
I am not understanding the question, what is mode, having the same fee
status? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... Bob I like your solution, what about if the mode could be H and has the same fee status as E. Thanks. "Bob Phillips" wrote: Assuming the fee table is in sheet 1, A1:H5, and the results table data is in sheet2 A1:E4, in Sheet2!E2, enter this formula and copy down =SUM(N(OFFSET(INDEX(Sheet1!$C$2:$H$5,0,MATCH(MIN($ B2,3)&$C2,{"1E","2E","3E","1O","2O","3O"},0)), MATCH(MID($D2,ROW(INDIRECT("1:"&LEN($D2))),1),Shee t1!$B$2:$B$5,0)-1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. Note that you cannot use a whole column in array formulae (prior to excel 2007), but must use an explicit range. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... Fee list per term Code description Code E (student yr =1) E (student yr =2) E (student yr 2) O (student yr =1) O (student yr =2) O (student yr 2) Full-time F 1,263.33 1,363.33 1,316.67 3,180.00 3,820.00 3,820.00 Part-time P 631.67 681.67 658.33 1,590.00 1,910.00 1,910.00 Field-work L 626.67 681.67 658.33 626.67 681.67 658.33 Part-time field-work Z 313.33 341.67 330.00 313.33 341.67 330.00 Student name Student yr Mode Pattern Fee (maually calculated) Tom 1 O FFF 9,540.00 Susan 2 E PPP 2,045.00 Sam 6 O PFF 9,550.00 I need help to write a function that would calcuate the fee column according to the above table, the fee charged is based on their pattern of study, their mode (O= overseas, E=home) and the student yr. The pattern of study is made up of three terms, the student can study any combination, i.e. Three terms fulltime (FFF), 2 terms part-time and 1term fulltime (PFP, PPF etc.). I have manually calculated the fee for you to confirm whether the function is returning the correct answer. Thanks. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function
Bob, thanks for all your help.
Student name Student yr Mode Pattern Fee (maually calculated) Susan 2 E PPP 2,045.00 Sam 2 H PPP 2.045.00 What I meant is in the column where I have "O" (Overseas) or "E" (home), I want to be able to insert "H" (Europe), the latter will have the fee charge as "E" (home). As per example above. Next question, can I use your array formula as below, IF(X1="PGR",Array formula,"") "Bob Phillips" wrote: I am not understanding the question, what is mode, having the same fee status? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... Bob I like your solution, what about if the mode could be H and has the same fee status as E. Thanks. "Bob Phillips" wrote: Assuming the fee table is in sheet 1, A1:H5, and the results table data is in sheet2 A1:E4, in Sheet2!E2, enter this formula and copy down =SUM(N(OFFSET(INDEX(Sheet1!$C$2:$H$5,0,MATCH(MIN($ B2,3)&$C2,{"1E","2E","3E","1O","2O","3O"},0)), MATCH(MID($D2,ROW(INDIRECT("1:"&LEN($D2))),1),Shee t1!$B$2:$B$5,0)-1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. Note that you cannot use a whole column in array formulae (prior to excel 2007), but must use an explicit range. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... Fee list per term Code description Code E (student yr =1) E (student yr =2) E (student yr 2) O (student yr =1) O (student yr =2) O (student yr 2) Full-time F 1,263.33 1,363.33 1,316.67 3,180.00 3,820.00 3,820.00 Part-time P 631.67 681.67 658.33 1,590.00 1,910.00 1,910.00 Field-work L 626.67 681.67 658.33 626.67 681.67 658.33 Part-time field-work Z 313.33 341.67 330.00 313.33 341.67 330.00 Student name Student yr Mode Pattern Fee (maually calculated) Tom 1 O FFF 9,540.00 Susan 2 E PPP 2,045.00 Sam 6 O PFF 9,550.00 I need help to write a function that would calcuate the fee column according to the above table, the fee charged is based on their pattern of study, their mode (O= overseas, E=home) and the student yr. The pattern of study is made up of three terms, the student can study any combination, i.e. Three terms fulltime (FFF), 2 terms part-time and 1term fulltime (PFP, PPF etc.). I have manually calculated the fee for you to confirm whether the function is returning the correct answer. Thanks. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function
OK, what we will do is just assume E if the Mode is H
=SUM(N(OFFSET(INDEX(Sheet1!$C$2:$H$5,0,MATCH(MIN($ B2,3)&IF($C2="H","E",$C2),{"1E","2E","3E","1O","2O ","3O"},0)),MATCH(MID($D2,ROW(INDIRECT("1:"&LEN($D 2))),1),Sheet1!$B$2:$B$5,0)-1,0))) and yes, you can incorporate it in an IF statement, just fremember you still need to array-enter it. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... Bob, thanks for all your help. Student name Student yr Mode Pattern Fee (maually calculated) Susan 2 E PPP 2,045.00 Sam 2 H PPP 2.045.00 What I meant is in the column where I have "O" (Overseas) or "E" (home), I want to be able to insert "H" (Europe), the latter will have the fee charge as "E" (home). As per example above. Next question, can I use your array formula as below, IF(X1="PGR",Array formula,"") "Bob Phillips" wrote: I am not understanding the question, what is mode, having the same fee status? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... Bob I like your solution, what about if the mode could be H and has the same fee status as E. Thanks. "Bob Phillips" wrote: Assuming the fee table is in sheet 1, A1:H5, and the results table data is in sheet2 A1:E4, in Sheet2!E2, enter this formula and copy down =SUM(N(OFFSET(INDEX(Sheet1!$C$2:$H$5,0,MATCH(MIN($ B2,3)&$C2,{"1E","2E","3E","1O","2O","3O"},0)), MATCH(MID($D2,ROW(INDIRECT("1:"&LEN($D2))),1),Shee t1!$B$2:$B$5,0)-1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. Note that you cannot use a whole column in array formulae (prior to excel 2007), but must use an explicit range. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... Fee list per term Code description Code E (student yr =1) E (student yr =2) E (student yr 2) O (student yr =1) O (student yr =2) O (student yr 2) Full-time F 1,263.33 1,363.33 1,316.67 3,180.00 3,820.00 3,820.00 Part-time P 631.67 681.67 658.33 1,590.00 1,910.00 1,910.00 Field-work L 626.67 681.67 658.33 626.67 681.67 658.33 Part-time field-work Z 313.33 341.67 330.00 313.33 341.67 330.00 Student name Student yr Mode Pattern Fee (maually calculated) Tom 1 O FFF 9,540.00 Susan 2 E PPP 2,045.00 Sam 6 O PFF 9,550.00 I need help to write a function that would calcuate the fee column according to the above table, the fee charged is based on their pattern of study, their mode (O= overseas, E=home) and the student yr. The pattern of study is made up of three terms, the student can study any combination, i.e. Three terms fulltime (FFF), 2 terms part-time and 1term fulltime (PFP, PPF etc.). I have manually calculated the fee for you to confirm whether the function is returning the correct answer. Thanks. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function
Thanks Bob.
Regarding the second question I tried, =if(G2="PGR",SUM(N(OFFSET(INDEX(Sheet1!$C$12:$H$16 ,0,MATCH(MIN($B2,3)&IF($C2="H","E",$C2),{"1E","2E" ,"3E","1O","2O","3O"},0)),MATCH(MID($D2,ROW(INDIRE CT("1:"&LEN($D2))),1),Sheet1!$B$12:$B$16,0)-1,0))),"") then Ctrl-Shift-Enter. I get an error. "Bob Phillips" wrote: OK, what we will do is just assume E if the Mode is H =SUM(N(OFFSET(INDEX(Sheet1!$C$2:$H$5,0,MATCH(MIN($ B2,3)&IF($C2="H","E",$C2),{"1E","2E","3E","1O","2O ","3O"},0)),MATCH(MID($D2,ROW(INDIRECT("1:"&LEN($D 2))),1),Sheet1!$B$2:$B$5,0)-1,0))) and yes, you can incorporate it in an IF statement, just fremember you still need to array-enter it. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... Bob, thanks for all your help. Student name Student yr Mode Pattern Fee (maually calculated) Susan 2 E PPP 2,045.00 Sam 2 H PPP 2.045.00 What I meant is in the column where I have "O" (Overseas) or "E" (home), I want to be able to insert "H" (Europe), the latter will have the fee charge as "E" (home). As per example above. Next question, can I use your array formula as below, IF(X1="PGR",Array formula,"") "Bob Phillips" wrote: I am not understanding the question, what is mode, having the same fee status? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... Bob I like your solution, what about if the mode could be H and has the same fee status as E. Thanks. "Bob Phillips" wrote: Assuming the fee table is in sheet 1, A1:H5, and the results table data is in sheet2 A1:E4, in Sheet2!E2, enter this formula and copy down =SUM(N(OFFSET(INDEX(Sheet1!$C$2:$H$5,0,MATCH(MIN($ B2,3)&$C2,{"1E","2E","3E","1O","2O","3O"},0)), MATCH(MID($D2,ROW(INDIRECT("1:"&LEN($D2))),1),Shee t1!$B$2:$B$5,0)-1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. Note that you cannot use a whole column in array formulae (prior to excel 2007), but must use an explicit range. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... Fee list per term Code description Code E (student yr =1) E (student yr =2) E (student yr 2) O (student yr =1) O (student yr =2) O (student yr 2) Full-time F 1,263.33 1,363.33 1,316.67 3,180.00 3,820.00 3,820.00 Part-time P 631.67 681.67 658.33 1,590.00 1,910.00 1,910.00 Field-work L 626.67 681.67 658.33 626.67 681.67 658.33 Part-time field-work Z 313.33 341.67 330.00 313.33 341.67 330.00 Student name Student yr Mode Pattern Fee (maually calculated) Tom 1 O FFF 9,540.00 Susan 2 E PPP 2,045.00 Sam 6 O PFF 9,550.00 I need help to write a function that would calcuate the fee column according to the above table, the fee charged is based on their pattern of study, their mode (O= overseas, E=home) and the student yr. The pattern of study is made up of three terms, the student can study any combination, i.e. Three terms fulltime (FFF), 2 terms part-time and 1term fulltime (PFP, PPF etc.). I have manually calculated the fee for you to confirm whether the function is returning the correct answer. Thanks. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function
The function is adequate for now, what about in future if I would like to
make the following changes, add new mode Q, which is charged the same as O I still cannot figure out how to incorporate your array formula, within the following IF function, =IF(X1=€PGR€,Your array formula,€ €) I have tried entering the above function, replacing Your array formula with yours, then pressing shift+cntrl+enter, but I get an error message. "Bob Phillips" wrote: OK, what we will do is just assume E if the Mode is H =SUM(N(OFFSET(INDEX(Sheet1!$C$2:$H$5,0,MATCH(MIN($ B2,3)&IF($C2="H","E",$C2),{"1E","2E","3E","1O","2O ","3O"},0)),MATCH(MID($D2,ROW(INDIRECT("1:"&LEN($D 2))),1),Sheet1!$B$2:$B$5,0)-1,0))) and yes, you can incorporate it in an IF statement, just fremember you still need to array-enter it. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... Bob, thanks for all your help. Student name Student yr Mode Pattern Fee (maually calculated) Susan 2 E PPP 2,045.00 Sam 2 H PPP 2.045.00 What I meant is in the column where I have "O" (Overseas) or "E" (home), I want to be able to insert "H" (Europe), the latter will have the fee charge as "E" (home). As per example above. Next question, can I use your array formula as below, IF(X1="PGR",Array formula,"") "Bob Phillips" wrote: I am not understanding the question, what is mode, having the same fee status? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... Bob I like your solution, what about if the mode could be H and has the same fee status as E. Thanks. "Bob Phillips" wrote: Assuming the fee table is in sheet 1, A1:H5, and the results table data is in sheet2 A1:E4, in Sheet2!E2, enter this formula and copy down =SUM(N(OFFSET(INDEX(Sheet1!$C$2:$H$5,0,MATCH(MIN($ B2,3)&$C2,{"1E","2E","3E","1O","2O","3O"},0)), MATCH(MID($D2,ROW(INDIRECT("1:"&LEN($D2))),1),Shee t1!$B$2:$B$5,0)-1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. Note that you cannot use a whole column in array formulae (prior to excel 2007), but must use an explicit range. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... Fee list per term Code description Code E (student yr =1) E (student yr =2) E (student yr 2) O (student yr =1) O (student yr =2) O (student yr 2) Full-time F 1,263.33 1,363.33 1,316.67 3,180.00 3,820.00 3,820.00 Part-time P 631.67 681.67 658.33 1,590.00 1,910.00 1,910.00 Field-work L 626.67 681.67 658.33 626.67 681.67 658.33 Part-time field-work Z 313.33 341.67 330.00 313.33 341.67 330.00 Student name Student yr Mode Pattern Fee (maually calculated) Tom 1 O FFF 9,540.00 Susan 2 E PPP 2,045.00 Sam 6 O PFF 9,550.00 I need help to write a function that would calcuate the fee column according to the above table, the fee charged is based on their pattern of study, their mode (O= overseas, E=home) and the student yr. The pattern of study is made up of three terms, the student can study any combination, i.e. Three terms fulltime (FFF), 2 terms part-time and 1term fulltime (PFP, PPF etc.). I have manually calculated the fee for you to confirm whether the function is returning the correct answer. Thanks. |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function
In the formula you will see this function
IF($C2="H","E",$C2) If you want to add a new mode with the same fees as E, change it to IF(OR($C2="H",$C2="Q")"E",$C2) If you want to add a new mode with the same fees as O, change it to IF($C2="H","E",IF($C2="Q","O",$C2)) If you want to add a new set of fees, add the 3 fee values to the end of the first table and then extend this array {"1E","2E","3E","1O","2O","3O"} to add the concatenated year and mode to the end, liks this {"1E","2E","3E","1O","2O","3O","1T","2T","3T"} The reason the formula doesn't work when adding an IF is that it creates too many nested functions, it works in principle, just that our formula is already as complex as Excel 2003 and earlier can handle. What you could do is put the array formula in say Y2, and in the Student table use =IF(X2="PGR",Y2,"") -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... The function is adequate for now, what about in future if I would like to make the following changes, add new mode Q, which is charged the same as O I still cannot figure out how to incorporate your array formula, within the following IF function, =IF(X1="PGR",Your array formula," ") I have tried entering the above function, replacing Your array formula with yours, then pressing shift+cntrl+enter, but I get an error message. "Bob Phillips" wrote: OK, what we will do is just assume E if the Mode is H =SUM(N(OFFSET(INDEX(Sheet1!$C$2:$H$5,0,MATCH(MIN($ B2,3)&IF($C2="H","E",$C2),{"1E","2E","3E","1O","2O ","3O"},0)),MATCH(MID($D2,ROW(INDIRECT("1:"&LEN($D 2))),1),Sheet1!$B$2:$B$5,0)-1,0))) and yes, you can incorporate it in an IF statement, just fremember you still need to array-enter it. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... Bob, thanks for all your help. Student name Student yr Mode Pattern Fee (maually calculated) Susan 2 E PPP 2,045.00 Sam 2 H PPP 2.045.00 What I meant is in the column where I have "O" (Overseas) or "E" (home), I want to be able to insert "H" (Europe), the latter will have the fee charge as "E" (home). As per example above. Next question, can I use your array formula as below, IF(X1="PGR",Array formula,"") "Bob Phillips" wrote: I am not understanding the question, what is mode, having the same fee status? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... Bob I like your solution, what about if the mode could be H and has the same fee status as E. Thanks. "Bob Phillips" wrote: Assuming the fee table is in sheet 1, A1:H5, and the results table data is in sheet2 A1:E4, in Sheet2!E2, enter this formula and copy down =SUM(N(OFFSET(INDEX(Sheet1!$C$2:$H$5,0,MATCH(MIN($ B2,3)&$C2,{"1E","2E","3E","1O","2O","3O"},0)), MATCH(MID($D2,ROW(INDIRECT("1:"&LEN($D2))),1),Shee t1!$B$2:$B$5,0)-1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. Note that you cannot use a whole column in array formulae (prior to excel 2007), but must use an explicit range. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... Fee list per term Code description Code E (student yr =1) E (student yr =2) E (student yr 2) O (student yr =1) O (student yr =2) O (student yr 2) Full-time F 1,263.33 1,363.33 1,316.67 3,180.00 3,820.00 3,820.00 Part-time P 631.67 681.67 658.33 1,590.00 1,910.00 1,910.00 Field-work L 626.67 681.67 658.33 626.67 681.67 658.33 Part-time field-work Z 313.33 341.67 330.00 313.33 341.67 330.00 Student name Student yr Mode Pattern Fee (maually calculated) Tom 1 O FFF 9,540.00 Susan 2 E PPP 2,045.00 Sam 6 O PFF 9,550.00 I need help to write a function that would calcuate the fee column according to the above table, the fee charged is based on their pattern of study, their mode (O= overseas, E=home) and the student yr. The pattern of study is made up of three terms, the student can study any combination, i.e. Three terms fulltime (FFF), 2 terms part-time and 1term fulltime (PFP, PPF etc.). I have manually calculated the fee for you to confirm whether the function is returning the correct answer. Thanks. |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function
Bob, Thanks a lot. I really appreciate all your help.
"Bob Phillips" wrote: In the formula you will see this function IF($C2="H","E",$C2) If you want to add a new mode with the same fees as E, change it to IF(OR($C2="H",$C2="Q")"E",$C2) If you want to add a new mode with the same fees as O, change it to IF($C2="H","E",IF($C2="Q","O",$C2)) If you want to add a new set of fees, add the 3 fee values to the end of the first table and then extend this array {"1E","2E","3E","1O","2O","3O"} to add the concatenated year and mode to the end, liks this {"1E","2E","3E","1O","2O","3O","1T","2T","3T"} The reason the formula doesn't work when adding an IF is that it creates too many nested functions, it works in principle, just that our formula is already as complex as Excel 2003 and earlier can handle. What you could do is put the array formula in say Y2, and in the Student table use =IF(X2="PGR",Y2,"") -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... The function is adequate for now, what about in future if I would like to make the following changes, add new mode Q, which is charged the same as O I still cannot figure out how to incorporate your array formula, within the following IF function, =IF(X1="PGR",Your array formula," ") I have tried entering the above function, replacing Your array formula with yours, then pressing shift+cntrl+enter, but I get an error message. "Bob Phillips" wrote: OK, what we will do is just assume E if the Mode is H =SUM(N(OFFSET(INDEX(Sheet1!$C$2:$H$5,0,MATCH(MIN($ B2,3)&IF($C2="H","E",$C2),{"1E","2E","3E","1O","2O ","3O"},0)),MATCH(MID($D2,ROW(INDIRECT("1:"&LEN($D 2))),1),Sheet1!$B$2:$B$5,0)-1,0))) and yes, you can incorporate it in an IF statement, just fremember you still need to array-enter it. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... Bob, thanks for all your help. Student name Student yr Mode Pattern Fee (maually calculated) Susan 2 E PPP 2,045.00 Sam 2 H PPP 2.045.00 What I meant is in the column where I have "O" (Overseas) or "E" (home), I want to be able to insert "H" (Europe), the latter will have the fee charge as "E" (home). As per example above. Next question, can I use your array formula as below, IF(X1="PGR",Array formula,"") "Bob Phillips" wrote: I am not understanding the question, what is mode, having the same fee status? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... Bob I like your solution, what about if the mode could be H and has the same fee status as E. Thanks. "Bob Phillips" wrote: Assuming the fee table is in sheet 1, A1:H5, and the results table data is in sheet2 A1:E4, in Sheet2!E2, enter this formula and copy down =SUM(N(OFFSET(INDEX(Sheet1!$C$2:$H$5,0,MATCH(MIN($ B2,3)&$C2,{"1E","2E","3E","1O","2O","3O"},0)), MATCH(MID($D2,ROW(INDIRECT("1:"&LEN($D2))),1),Shee t1!$B$2:$B$5,0)-1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. Note that you cannot use a whole column in array formulae (prior to excel 2007), but must use an explicit range. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nc" wrote in message ... Fee list per term Code description Code E (student yr =1) E (student yr =2) E (student yr 2) O (student yr =1) O (student yr =2) O (student yr 2) Full-time F 1,263.33 1,363.33 1,316.67 3,180.00 3,820.00 3,820.00 Part-time P 631.67 681.67 658.33 1,590.00 1,910.00 1,910.00 Field-work L 626.67 681.67 658.33 626.67 681.67 658.33 Part-time field-work Z 313.33 341.67 330.00 313.33 341.67 330.00 Student name Student yr Mode Pattern Fee (maually calculated) Tom 1 O FFF 9,540.00 Susan 2 E PPP 2,045.00 Sam 6 O PFF 9,550.00 I need help to write a function that would calcuate the fee column according to the above table, the fee charged is based on their pattern of study, their mode (O= overseas, E=home) and the student yr. The pattern of study is made up of three terms, the student can study any combination, i.e. Three terms fulltime (FFF), 2 terms part-time and 1term fulltime (PFP, PPF etc.). I have manually calculated the fee for you to confirm whether the function is returning the correct answer. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions |