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.