complex percentages formula

Question

complex percentages formula

Hi everyone. So I am making a number file to calculate the budget for a small training center.

There are the basic columns like revenues - expenses - final revenues. We have a calling center that will promote for us. They ask a percentages on the subscriptions. Their fees are 10% per attendees, 12% per attendees if the first class is filled. Then, it will be 12% per attendees and 15% for each other classes that are filled. And finally, if they filled all the classes available in the month, the ask 20%.

1st column I have all the dates.

2nd columns are number of attendees

3rd is the price of the training

4th is the revenues

5th is the commission.

So far, the commission column has this formula : IF(B4=10;D4×B22;D4×A22)

What kind of if statement I would need so it work that way :

A22 (10%) for each sales.

B22 (12%) if a first class is filled and each next sales also get B22 (12%)

Each other classe that are filled get C22 (15%)

If the whole month is filled, then it's D19*D22

Does that make sense?

Thanks for your help 🙂

MacBook Pro with Retina display, OS X Yosemite (10.10), null

Posted on Mar 25, 2015 4:38 PM

Reply

Answer 1

Best reply

charles.christian14

Level 1

147 points

Mar 26, 2015 9:44 AM in response to cedricfrommontreal

i have tried something here and you can tell me if I am close or way off:

It might be easier for you to calculate the total at the end of the month. The first table is just a re-creation of your table, and the second is where the meat is. Notice that it is important to erase the separators between the dates. This will mess up the calculations.

	Participants	Cost	Money/Class	Commission
Date 1	10	$142.50	$1425.00
Date 2	3	$142.50	$427.50
Date 3	4	$142.50	$570.00
Date 4	6	$142.50	$855.00
Date 5	10	$142.50	$1425.00
Date 6	9	$142.50	$1282.50
Date 7	4	$142.50	$570.00
Date 8	10	$142.50	$1425.00
Date 9	8	$142.50	$1140.00
Date 10	10	$142.50	$1425.00
	74		$10545.00	$1258.28

Table 2

Notice that in table two the cells:

A4: 10% because that is the base

B4: 2%, because it is 12% - 10%

C4: 5% because it is 15% - 10%

D4: 20% because if all classes are full then this is the base.

Column A is the total number of people that attended the event. The sum of Column B table 1.

Column B: Cell B2 locates which row the first full class (10) is located using Match(10,Column B::Table 1, -1). Cell B3 adds the remaining participants after this first full class. I set it up that way since I read your instructions that after the first full class the commission from there on is 12%; the formula is Sum(OFFSET(Cell B2::Table 1, B2, 0 , counta(Column B::Table 1) - 1)).

Column C: Cell C2; Counts the number of full classes (10) using Countif(column B::Table 1,10). Cell C3 subtracts one since there is 15% for every full class after the first one.

Column D: Cell D2; Uses a CountA formula to count the total number of classes in the month, and multiplies that number by 10 ( the number for a full class). Cell D3; implements an if formula to if cell D2 = Cell B11::Table 1. If(D2=Cell B11::Table 1, 1, 0).

Cell A5: Total Commission: =IF(D3 = 1, D4 * cell C11::table 1, (A3*A4+B3*B4+C3*C4)*142.50)

Number of Participants	Row where first full class	Number of full classes	Max Total Participants
	2	4	100
74	64	3	0
10%	2%	5%	20%
1258.275


10% for every one
12% for everyone after first full class
15% for every full class after the first
20% if all classes are full

Reply

Answer 2

Mar 26, 2015 10:17 AM in response to charles.christian14

I understand pretty much everything you did and that will do the job perfectly. Thank you a lot!!!!!

One last question, is there a reference somewhere for the equivalent formulas in other language? I tried to add the MATCH formula in my table, but because I have Numbers in french, it doesn't recognize it.

Reply

Answer 3

Tom Gewecke

Level 10

115,830 points

Mar 26, 2015 10:35 AM in response to cedricfrommontreal

cedricfrommontreal wrote:

is there a reference somewhere for the equivalent formulas in other language?

http://help.apple.com/functions/mac/5.0/

and use the "change language" button

Reply

Answer 4

Mar 26, 2015 11:21 AM in response to Tom Gewecke

Thank you for this answer but that won't make it. I'm trying to find a reference where if I select MATCH, I would see the equivalent in the other language. The link you gave me would be useful if I would know the name of the function in my language. Or I don't understand How the page work? 🙂

Reply

Answer 5

Tom Gewecke

Level 10

115,830 points

Mar 26, 2015 11:46 AM in response to cedricfrommontreal

cedricfrommontreal wrote:

Or I don't understand How the page work? 🙂

I will explain it. You first find MATCH in the categories on the page when it is in English (it is in Reference Functions). Then you switch the language to French and go to the same category and find the equivalent term (EQUIV).

Reply

Answer 6

Mar 26, 2015 11:59 AM in response to Tom Gewecke

Oh I see, I dont go in the reference, I stay in the main page and find the function. I thought they might not be in the exact same position. Thanks!

Reply

Answer 7

Mar 27, 2015 3:55 AM in response to cedricfrommontreal

Hey Cedric,

I just realized a flaw in the way it is set up now. It is gives both 12% commission and 15% commission to the full classes after the first full class. To fix this is simple. In Cell B3 add this to the end of the formula: - C3, which subtracts the number of students in full classes after the first. And change Cell C3 to:

(C2-1)*10, which represents the number of people in the full classes after the first. This is more correct.

Number of Participants	Row where first full class	Number of full classes	Max Total Participants
	2	4	100
74	34	30	0
10%	2%	5%	20%
1365.15


10% for every one
12% for everyone after first full class
15% for every full class after the first
20% if all classes are full

Reply