Large, Multi-Condition Formula Problems

[jlurton]

Below are the first few lines of a spreadsheet that I’ve created to help with payroll calculations for a friends yoga studios with one new and problematic addition. The spreadsheet has worked well to date but they’ve asked for a new feature that I’m struggling with. They’ve asked for a way to track new student signups, the new column “I”. Sounds simple enough, right?

Here’s my problem, there are three different payment structures:

Regular Classes, with attendance recorded in F, G, H, and now with new students in I.
Students pay per class, either $5 or $10, or Monthly. New students get their first class free.
Teachers are paid per class based on the total number of students, including new, per the following schedule:
4 or fewer = $25, 5 to 9 = $35, 10 to 18 = $40, 19 or more = $45

Community Classes, with attendance recorded in E and now with new students in I.
Students pay $5 per class. New students get their first class free.
Teachers are paid per class based on the total number of students, including new, per the following schedule:
60% of total students x $5

Workshops, with only total $ recorded in L.
Students pay a pre-determined fee that changes with each workshop.
Teachers are paid 60% of the total taken in.
There are generally only a few of these a month.

What I’m trying to do now is integrate the requested new column I to record new students. Column I serves double duty for new students in both Regular and Community classes. It is not used for workshops. I’m stuck on how to add the additional conditions to my pay calculation formulas in column K. Ideally, I would like to roll all three class types into one formula, including the workshops, but that is beyond my current capabilities. I think what would work would be a formal in K that says if there are numbers in E, calculate ((E2+I2)*5)*0.6, if there are numbers in F, G, or H, calculate IF(AND(J2>0,J2<=4),25,IF(AND(J2>=5,J2<=9),35,IF(AND(J2>=10,J2<=18),40,IF(AND(J2>=19,J2>18),45, if there’s a number in L, calculate L2*.6 .

This seems reasonable but the syntax is above and beyond my knowledge. If I can’t get this sorted, I’ll split the formulas up between class types, which is a workable, but less than ideal, solution. Another part of the spreadsheet calculates income from students, but that has been incorrect because new students don’t pay for their first class.
Any help would be much appreciated.

A

B

C

D

E

F

G

H

I

J

K

L

M

1

Date

#

Class

Teacher

Comm

Five

Ten

Monthly

New

Total

Pay

Workshop

60%

2

8/1/2015

27

Saturday 9:00-10:30am

Brian

0

$ -

$ -

$ -

3

8/1/2015

28

Saturday 10:45-12:30pm

Brian

0

$ -

$ -

$ -

4

8/2/2015

29

Sunday 9:00-10:15am

Jessica

0

$ -

$ -

$ -

<tbody>
</tbody>

The Formulas
J2: =SUM(F2:I2)
K2: =IF(AND(J2>0,J2<=4),25,IF(AND(J2>=5,J2<=9),35,IF(AND(J2>=10,J2<=18),40,IF(AND(J2>=19,J2>18),45,((E2+I2)*5)*0.6))))
M2: =L2*0.6

 

[AlanY]

try this in K2 and copy down

=INDEX({25,35,40,45},MATCH(1,FREQUENCY(J2,{4,9,18}),0))+E2*5*0.6

 

[AlanY]

try this in K2 and copy down

=INDEX({25,35,40,45},MATCH(1,FREQUENCY(J2,{4,9,18}),0))+E2*5*0.6

sorry, should be

=INDEX({25,35,40,45},MATCH(1,FREQUENCY(J2,{4,9,18}),0))+(E2+J2)*5*0.6

 

[AlanY]

try this in K2 and copy down

=INDEX({25,35,40,45},MATCH(1,FREQUENCY(J2,{4,9,18}),0))+E2*5*0.6

sorry, should be

=INDEX({25,35,40,45},MATCH(1,FREQUENCY(J2,{4,9,18}),0))+(E2+J2)*5*0.6

after reread your first post again, i believe the formula in Post#2

=INDEX({25,35,40,45},MATCH(1,FREQUENCY(J2,{4,9,18}),0))+E2*5*0.6

should be used

but still not quite right if the new students in Column I are the combination of the regular and comm classes. you need a way to distinguish them in order to get the total pay correctly.

 

[AlanY]

sorry, should be

=INDEX({25,35,40,45},MATCH(1,FREQUENCY(J2,{4,9,18}),0))+(E2+J2)*5*0.6

this will only work if all the new students in Column J attended both regular and comms classes, otherwise you need to seperate Column J into 2.

 

[jlurton]

Originally Posted by AlanY
sorry, should be

=INDEX({25,35,40,45},MATCH(1,FREQUENCY(J2,{4,9,18}),0))+(E2+J2)*5*0.6

this will only work if all the new students in Column J attended both regular and comms classes, otherwise you need to separate Column J into 2.

Thanks for the reply. INDEX/MATCH is new to me to it's taking me a little to digest this but I am running into a few problems.

When I have a value in E only for community classes, the formula adds 25 to the total.

The other problem is that the teacher pay should cap at $45 for >18 students but the pay in K continues to increase if I enter in larger numbers.

I'll have more time to dig into it later tonight so I may figure something out then.

Any thoughts?

Thanks again

Date	#	Class	Teacher	Comm	Five	Ten	Monthly	New	Total	Pay
8/1/2015	27	Saturday 9:00-10:30am	Brian	20					0	$ 85.00 This should be $100
8/1/2015	28	Saturday 10:45-12:30pm	Brian						0	$ 25.00 Correct
8/2/2015	29	Sunday 9:00-10:15am	Jessica		1				1	$ 28.00 This should be $25
8/2/2015	30	Sunday 10:45am-12:15pm	Kristen		10				10	$ 70.00 This should be $40
8/3/2015	1	Monday 6:00-7:00am	Jess		50				50	$ 195.00 This should be $45

<tbody>
</tbody>

 

[AlanY]

but you still have a problem, say with 10 new students with some for the regular class, some for the comms and some for both.
unless you seperated them it's not possible to work out the teacher's correct fee.