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