Need a formula for 2 different percentages in one formula

218 Views 10 Replies Latest reply: Jan 5, 2014 12:59 AM by greg1424
Level 1 (0 points)
Currently Being Moderated
Jan 4, 2014 10:03 PM

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)
• Level 4 (3,880 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.

• Level 4 (3,880 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.

• Level 4 (3,880 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.

• Level 4 (3,880 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.

• Level 4 (3,880 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.

More Like This

• Retrieving data ...

Legend

• This solved my question - 10 points
• This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.