
1. Re: Need a formula for 2 different percentages in one formula
Yellowbox Jan 4, 2014 10:34 PM in response to greg1424Hi 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
greg1424 Jan 4, 2014 10:56 PM in response to YellowboxI 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 67 & 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
Yellowbox Jan 4, 2014 11:31 PM in response to greg1424Hi 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
greg1424 Jan 4, 2014 11:46 PM in response to YellowboxOk
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
greg1424 Jan 5, 2014 12:04 AM in response to greg1424One 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 3I 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.
Thanks for all your help
Greg

6. Re: Need a formula for 2 different percentages in one formula
Yellowbox Jan 5, 2014 12:10 AM in response to greg1424Hi 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
Yellowbox Jan 5, 2014 12:22 AM in response to greg1424Hi 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 13 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
greg1424 Jan 5, 2014 12:34 AM in response to Yellowbox(1)
Yes I can get rid of the row 13, in fact it will make it a little bit bigger to read
great Idea, thanks
(2)
Your quote:
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
Yellowbox Jan 5, 2014 12:53 AM in response to greg1424Hi Greg,
I suggested getting rid of rows 13 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
greg1424 Jan 5, 2014 12:59 AM in response to YellowboxI 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