Excel formula help, please.

[bodminman]

Hi there,

 

I have been asked to try and sort out a formula to use various bits of info to calculate commission costs.

 

Unfortunately, I'm no Excel whizz so thought I'd ask you very knowledgeable people for assistance.

 

 

What we want to do is -

 

If student is in year group = 7-9, 10-11 or 12-13

 

Look up term fees for the relevant year group, in a grid at the bottom of the .xlsx, multiply by the number of terms in the Duration (Terms) column, and then deduct Tuition Scholarship %.

 

Look up boarding fees for the relevant year group (In a grid at the bottom of the .xlsx, multiply by the number of terms in the Duration (Terms) column, and then deduct Boarding Scholarship %.

 

Add the two figures together and then work out the Agent Commission Cost £, based on what % is in the Agent Commission % column.

 

Your assistance will be greatly appreciated.

 

Best wishes

[TechMonkey]

Just off the top of my head I would move the grid to it's own sheet, a reference sheet. If the grid has the lower limits of the year groups you can use a VLOOKUP and it will figure out the range it is in BUT for future proofing I would create a row per year as there aren't many and specifically assign a value to each row.

 

So the first formula would look something like

=(VLOOKUP(YEARCOLUMN, REFERENCESHEET!TABLEARRAY,COLUMN_RETURN_VALUE_IS_IN,TRUE)*DURATION)-SCHOALRSHIP

 

Got to run but hopefully that sets you on the right path

[howartp]

Agree with what @TechMonkey said.

 

Except I'd say FALSE not TRUE at the end of vlookup, particularly if using a row per year group.

 

See attached (sorry - updated attachment)

TermFees.xlsx

Edited June 21, 2022 by howartp

[bodminman]

@TechMonkey & @howartp

 

Thank you so much for your assistance. With your help, I managed to complete the task this morning.

 

Best wishes