10 Replies Latest reply: Jan 5, 2014 12:59 AM by greg1424
Level 1 (0 points)

Need a formula for 2 different percentages in one formula

I have a situation where I need to have 2 different sales commission percentages in a formula for the sales commission line.

I  know how to do it for a single percentage and would prefer not to have 2 separate rows (line item) for the different %.

In other words:

I have 5 sales (product) lines, 2 are at 3% and 3 are at 5% commission, I want to combined them in the sales commission line (row).

Is this possible, I have tried a few scenarios but none of them worked for me.

Thanks

Greg

OS X Mountain Lion (10.8.2)
• ###### 1. Re: Need a formula for 2 different percentages in one formula
Level 5 (4,560 points)

Hi Greg,

Please describe your table. Are the products listed in a column or a row? How are the 3% and 5% commissions identified?

Regards,

Ian.

• ###### 2. Re: Need a formula for 2 different percentages in one formula
Level 1 (0 points)

I will try and i added a screen shot:
The sales rows (lines) are the ones that will have the commissions,

Lines (row)  5 & 8 are at 3% and lines 6-7 & 9 are at 5%

The sales commission is on Line (row) 33

Therefore:
I want to have line 33 have the 2 different sales commissions on the same line if possible.

All the rows are in the same column.

• ###### 3. Re: Need a formula for 2 different percentages in one formula
Level 5 (4,560 points)

Hi Greg,

Perhaps another table will help to calculate total commissions. This table can be "hidden" by moving it to another sheet. Here, the image at the top is a screen shot of part of your screen shot to help me create the new table.

Formula in D2 of the new table (and Fill Down to all Body Cells):

=B2×C2÷100

Formula in D8 =SUM(D)

Row 1 is a Header Row, and Row 8 is a Footer Row. They are excluded from the SUM formula (that will SUM only the Body Cells in Column D).

D8 is the value that I think you want for Row 33 of your original table. B8 is just a checksum to compare with your original table.

Regards,

Ian.

• ###### 4. Re: Need a formula for 2 different percentages in one formula
Level 1 (0 points)

Ok

I couple of questions:
(1)
How do I get a hidden sheet?
Can I just add a sheet and when I print or do a PDF I will not include those sheets aka Sheet2

(2)
The way I calculate a % is : =B2×0.03, I take it since I am using columns do I need to do it your way?

(3)

How do I get the sum D8 over to my row 33?

Can I simple do a Function for row 33 (Sales Commission) and highlight the D8 column (or whatever the column and row number is.

In other words: will a function carry over from Sheet2 to Sheet 1

I hope I made myself clear, I am 70 and trying to help a friend who is older then me and even less computer knowledgeable then me.

• ###### 5. Re: Need a formula for 2 different percentages in one formula
Level 1 (0 points)

One other thing:
It is a 5 year cash flow, so that means that I will have to have sheet2 with 180 columns which is the sales column, commission % column and Commission column 60 months x 3

I hope when you answer my questions (previous post) that i will be able to make the sales rows in column B in Sheet2 a function and have the main sheet1 highlighted and carry over to sheet2 so that I do not have to manually change any number if I change the sales numbers.

I think I may be better off just making another row and have 2 sales commission rows.

Greg

• ###### 6. Re: Need a formula for 2 different percentages in one formula
Level 5 (4,560 points)

Hi Greg,

(1)

How do I get a hidden sheet?

Can I just add a sheet and when I print or do a PDF I will not include those sheets aka Sheet2

Correct!

(2)

The way I calculate a % is : =B2×0.03, I take it since I am using columns do I need to do it your way?

You don't need to do it my way. B2x0.03 is fine. I suggested another column for commission % in case the commission rate changes. Then you can revise the rate without editing the formula.

(3)

How do I get the sum D8 over to my row 33?

Click on the cell in Row 33 (original table), type = and then click on D8 (new table). It is easier to do this if both tables are on the same sheet, then you can move the new table (select, Cut, go to a new sheet, Paste). The formulas automatically adjust when you move a table. No problem if the new table is already on a new sheet. The formulas will link correctly. To summarise:

In other words: will a function carry over from Sheet2 to Sheet 1

Yes!

I hope I made myself clear, I am 70 and trying to help a friend who is older then me

I am 65 and fast catching you up!

Regards,

Ian.

• ###### 7. Re: Need a formula for 2 different percentages in one formula
Level 5 (4,560 points)

Hi again, Greg,

It is a 5 year cash flow, so that means that I will have to have sheet2 with 180 columns which is the sales column, commission % column and Commission column 60 months x 3

Umm... the document is getting a bit large. Consider splitting it into several small documents, each with a summary table. You won't be able to link between documents, but copying each summary table to a master document (Paste Formula Results) will give you the overall view.

i will be able to make the sales rows in column B in Sheet2 a function and have the main sheet1 highlighted and carry over to sheet2 so that I do not have to manually change any number if I change the sales numbers.

Yes. Column B of the new table can link back to the original table. For example, B2 of the new table = B5 of the original table.

Just a thought: do you really need Rows 1-3 of the original document, or could they be replaced by text boxes? Then the Row numbers between tables will match and make editing easier.

Regards,

Ian.

• ###### 8. Re: Need a formula for 2 different percentages in one formula
Level 1 (0 points)

(1)

Yes I can get rid of the row 1-3, in fact it will make it a little bit bigger to read

great Idea, thanks

(2)

Umm... the document is getting a bit large. Consider splitting it into several small documents, each with a summary table. You won't be able to link between documents, but copying each summary table to a master document (Paste Formula Results) will give you the overall view.

Not sure how to do this,

Are you referring to the commissions sheet, because I already have a 120 month (10 year) setup for the sheet1 (Main Cash Flow) and when I print it each year prints on a page.

I am only using the 5 years as I set this up last year for another project.

Is the printing your concern ?

• ###### 9. Re: Need a formula for 2 different percentages in one formula
Level 5 (4,560 points)

Hi Greg,

I suggested getting rid of rows 1-3 as Numbers works best with small, discrete tables that contain data of a similar kind. Excel (which I used for years) gives us an "ocean" of a sheet with "islands" of data dotted here and there. (Praise to Barry in this forum for that concept).

Printing was not my concern, and you seem to have that worked out. I was thinking that Numbers is suited to small documents with an input table, an "engine room" table that does the number crunching, and a "presentation" table that summarises the results.

If your set up works, stick with it!

Thanks for your replies and the green tick. Any other problems, please call again. There are many Numbers gurus in this forum, but perhaps they are asleep. They don't all use Australian time (8 pm, Sunday 5 January here) .

Regards,

Ian.

• ###### 10. Re: Need a formula for 2 different percentages in one formula
Level 1 (0 points)

I live in Texas and consider sleeping a waste of time, it is 3:00 am here.

I was born and raised in NYC, the City that NEVER sleeps, so that is in my DNA.

LLOL