Please help, getting so confused!

[tarynmahon]

Hi, Im getting so confused joining IF's together, does anyone have a more simplified way of writing my formula please?
If I can get the formula in column Q right I can sort everything else, basically there are so many variables that need to be included please see below;

Column BH-BM gives how many days I need to apportion the below figures by, if BH:BK-BM:BP = 366 days then no need to apportion.

In column M - If "Fees" or "Mphil Fees" return 0
- If "Full" return -FEES-H12
- If "Full - Enhanced" return -FEES-H15
- If "Full - Enhanced +" return -FEES-H16
- If "MPhil" return -FEES-H17

In column F - If "FT" return as above
- If "PT" return as above *.6

In column N - If "Yes" return the result of both column M & F *.5

Please help!

 

[Luke M]

Hi tarynmahon.

You can make you things incredibly easy if we build a small table on the -FEES- sheet. Check out in the attached, FEES range K12:L17. I setup the outputs for what you described.

Then, your formula in col Q of the sheets becomes:
=VLOOKUP($M4,'-FEES-'!$K$12:$L$17,2,)*IF($F4="PT",0.6,1)*IF($N4="Yes",0.5,1)*($BH4-$BM4)/366

Which can be read pretyy much left to right. First, go to our table and get correct value. Next, check col F if we should multiply by 0.6, then to col N to see if we multiply by 0.5, and finally do the appropriation. Note that if BH4:BM4 = 366, then the final step evaluates to 1, so we're still good.

Note that on your AHRC sheet, the last two rows don't have a value for the col M, which is causing an error.

 

[tarynmahon]

Wow, you really are a Ninja!
Is it too much to express my undivided love for you?!

Thank you so much!
Taryn

 

[Luke M]

You're very welcome, come back anytime.