# Thread: Get average of percentages

1. Steve, here's a sample where there is only one entry of the labor billing with no expenses.

2. What do you want the %Profits to be when there are no parts? Your calculation is (Parts-Cogs)/Parts and all are zero. I think the Div/0 error is appropriate and completely accurate

If you want it to be 0% like your intermediate calculation shows, then use:
=IF(SUMPRODUCT((\$A\$5:\$A\$439="Parts")*(\$D\$5:\$D\$439+ \$I\$5:\$I\$439))=0,0,(SUMPRODUCT((\$A\$5:\$A\$439="Parts" )*(\$D\$5:\$D\$439+\$I\$5:\$I\$439))-SUMPRODUCT((\$A\$5:\$A\$439="Cogs")*(\$D\$5:\$D\$439+\$I\$5: \$I\$439)))/SUMPRODUCT((\$A\$5:\$A\$439="Parts")*(\$D\$5:\$D\$439+\$I\$5 :\$I\$439)))

Steve

3. kzkz,

Actually meant to create a pop-up confirmation form to verify before clearing sheet. What would be a very doable thing would be to place a button that will create a new tab named with the following week and the date on the form itself so that if you print it, you will know which week it refers to. By the way, what does COGS stand for?

By the way, the nice thing about an update button is that you can verify that the entries you made have taken place by the visual change in the calculated values. You will not see that with cell formulas if you have scrolled down to where the calculated values are no longer visible. Additionally, all calculations can be tied to one update button so its a one click operation.

Maud

4. Maud, COGS stands for Cost of Good Sold. It's the normal accounting name used for what the parts cost that you're going to sell. Sorry, I'm one of those "bean counter" accounting guys.
That would be really cool if you could do those updates with the popup warning and also the new tab thing.

5. Maud, What about when/if the billing parts is zero, but the Cost of Goods Sold is \$50? That returns an error.

6. kzkz,

OK, modificatios made. If you decide to use it, read on.....
1. A warning pop-up will appear to confirm that the user wants to clear the data (not formulas)
2. An "Add New Form" button on the sheet. When the user clicks this button, they will be presented with a calendar pop-up to select the starting date. If they select anything other than a Monday, a message will direct them to try again. If they select a date on a Monday a new worksheet will be created and named with "Week of date" and the date will automatically be entered in the left upper corner of the sheet. The calendar pop-up is a MS control. It should have been installed with Office 2010 but if you do not have it, it will fail. Let me know as there are simple alternatives
3. I have added additional blank buttons for future upgrades that don't do anything at this time
4. If you print the worksheet, the buttons will not print
5. Removed the name "PR" as this caused a problem when attempting to copy the worksheet. I could not find it used in any formulas

Things not to do:
1. Do not add additional rows or columns as it will desynchronize the row/column lookup for values
2. Do not add names for ranges. This will interrupt he copying of the sheets to confirm the scope of the name extended to the new sheet
3. Do not remove or rename the Template worksheet. This is where the new sheet will be copied from. You can remove or rename any other sheet.
4. Do not password protect the sheet as this will lock cells that the code will attempt to change the values. If you wish to lock the sheet, give me a password that you will not change and I will write it into the code to unlock the sheet on activation to change values, then relock the sheet with the same password.

Things you should do:
1. Make a master backup of the sheet
2. Email me via Windows Secrets profile that I give you my real email address if you have any questions.

HTH,
Maud

7. ## The Following User Says Thank You to Maudibe For This Useful Post:

kzkz (2012-09-23)

8. Fantastic! I love it. I'll PM you now with a few questions and a pw to protect the sheet. I like to protect documents that have formulas so that others don't over-write them.

9. Whoa! Stop and think about this. Averages of percentages are NOT NOT NOT statistically valid. Not ever. (OK, with a good imagination, you can think of one--but it would be very unusual circumstances.)

10. Hello, statistically what you are asking to do is meaningless. You can perform any arithmetic operation you choose. The question to be answered is do you want meaningful data?

11. Have you ever said to yourself, I am right on an average of 75% of the time? If you have 4 tasks with each having 4 decisions. If you were right in 50 % of the decisions in task 1, 75% right in the decisions for task 2, 100% for task 3, and 95% for task 4, why would it not be correct or meaningless to say that on average, you are 80% correct when it comes to doing tasks?

12. That works because it is a special case where task has 4 decisions. In any other case (ie most of the time !) your calculation will give an erroneous result

On the other hand if you have two investments:

One is worth £10 and grows 20% whilst the other is worth £1000 and grows 10%.

It would not be correct to say that on, average, your investments have grown by 15%. 10.1% would be nearer the mark.

The correct weighted formula works for both your example and mine - your formula only works for your example.

13. What if you were looking at a 10 year span? On average, my investment grew 10% per year. Sounds like a meaningful measurement to me!
If http://www.dinkytown.net/java/ProfitMargin.html use average percentages, then it is good enough for me.

14. I'm not looking for an answer to make huge financial decisions on, but just a good rough number that tells me that the profits for that week on parts and or labor has been in a certain range and then compare it to the next week and so on. When one job you make 40% profit then another 35%...I want to know just an average of the two which sounds pretty simple to me.

15. kzkz,

One job at 40% and the other at 35% . . . the average profit is 37.5% ONLY IF both job are worth the same amount. In any other case 37.5% is NOT the true average.

It is indeed pretty simple to get the true average as all the data is in your spreadsheet. I suspect that doing so will be easier and less error-prone than the clearly difficult route you have been going down:

1. For each transaction calculate the Profit
2. Add up all the individual Profits to get the Total Profit
3. You presumably have the selling price for each transaction, so add up the selling prices ie Total Sales
4. Divide the Profit by the Total Sales
5. Hey presto - that is the accurate average profit.

The beauty of simple adding up is that it is immune to oddities such as blank entries and so on.

16. ## The Following User Says Thank You to MartinM For This Useful Post:

ruosChalet (2012-10-15)

17. kzkz,
As tempting as a nice simple number can be, it is misleading. It is not statistically valid because you are calculating a statistic on non-homogeneous items (apples, oranges, ...).
One assumption buried in your logic is that the amount of labor that should be expended is proportional and consistent to the cost of the parts used. I can assure you that the designers were not trying to standardize this value in a single item, let alone across different items. It also does not account for an experience on the part of the repair personnel. eg. 'I saw this problem before, I know what to check.'
Further, the measurement does not truly indicate when you have a problem. You may say you can see when the percentage is different, but you can't see why and it will never indicate when you truly have a problem but the percentage is in 'normal' range.
Further, a repair that involves labor without replacing parts is truly valid in many mechanisms.
-- Without further detail, it is a really, really bad and invalid measurement. As a continuous improvement manager and consultant I can tell you this is one of those measurements that makes someone a hero one period and a goat that gets called on the carpet the next period; without them having any real control or appropriate action to 'correct' the number. It leads people and companies to enact stupid policies to try to fix the number to CYA when the number has no real life bearing on customer satisfaction or profitability.

18. ## The Following User Says Thank You to Han For This Useful Post:

ruosChalet (2012-10-15)

Page 3 of 3 First 123

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•