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.