Need a formula for 2 different percentages in one formula

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.

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.

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.

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.

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.