# Thread: Get average of percentages

1. Maudibe If I use this formula =AVERAGEIF(G1:G18,">0") How would I put that in if there were cells not next to each other? I attempted to use the Named cell function, but couldn't get that to work correctly.

2. Your formulas will not work:Average(a+b+c...) is not the same as Average(a,b,c...).
The average((a+b+c...) = average(sum(a+b+c...)) = Sum(a+b+c...). You need to do commas not pluses.

To get a formula that works for M1 you can use:
=AVERAGE(IF(MOD(ROW(\$F\$5:\$F\$439),8)=1,\$F\$5:\$F\$439) )+AVERAGE(IF(MOD(ROW(\$K\$5:\$K\$439),8)=1,\$K\$5:\$K\$439 ))

For N1 you can use:
=AVERAGE(IF(MOD(ROW(\$F\$5:\$F\$439),8)=0,\$F\$5:\$F\$439) )+AVERAGE(IF(MOD(ROW(\$K\$5:\$K\$439),8)=0,\$K\$5:\$K\$439 ))

Though I do not understand why you want to add 2 averages...
[If you do not the zeroes to be averaged you can do what I indicated earlier and change them to a null string...]

For O1:=SUM(IF(MOD(ROW(\$F\$5:\$F\$439),8)=2,\$F\$5:\$F\$439)) +SUM(IF(MOD(ROW(\$K\$5:\$K\$439),8)=2,\$K\$5:\$K\$439))

Steve

3. Originally Posted by kzkz
I attempted to use the Named cell function, but couldn't get that to work correctly.
Did you select the appropriate non-contiguous ranges of cells, and name them?

Steve

4. Please see my previous post revised with VB code and a updated spreadsheet with the code in place.

Maud

5. sdckapr I didn't see what you were talking about there because I'm not trying to get an average of the whole column vs the other column, but only certain cells that pertain to either the COGS profits or the total job profits. So, the sum in M1 is F9+F17+F25+F33+F41,. etc for the total profits then the COGS summary is in N1 coming from F8+F16+F24+F32+F40, etc.
Then the sum in O1 is to actually to be a total sum of all the profit dollars from the whole day, so that one actually works correctly.

6. Maudibe, Wow, Thanks!
That seems to do the trick for the most part, I'm trying to understand some of the in case I need to edit anything.
1) When all of the cells are blank, and you hit the update, it returns a "run-time error'6" overlfow"
2) Iit doesn't seem to update the numbers for all of the page. That's because I had an additional divider line for the days of the week through through your formula out of whack. I fixed that.
3) What exactly is this doing? I guess I REALLY don't understand macros How is the columns F and K calculated with the formula I and J?

For I = 6 To 11 Step 5 'cylce between the 2 columns F and K where the Labor Hours are located
For J = 9 To 438 Step 8 'cylce between the many rows where the Labor Hours are located

7. kzkz: You are most welcome!

Row 6 and 11 are the columns where the labor hours and Cogs are located. These are loops that increment values to determine the cells to look at. There are 2 loops. The first:
For I = 6 To 11 Step 5 says look at row 6 then do all the commands then jump to 11 then do the commands again. Inside that is the second loop: For J = 9 To 438 Step 8. Here, the code is looking at the rows starting with 9 then jumping to 17 and so on in increments of 8. These are the rows that those values are on. The code worked perfectly for me, I will check it out again why you are getting those errors the overflow error in my next post. Have you added any additional lines and/or columns? Have you changed or redesigned anything?

Maud

8. I see where the problem was. There are extra rows for the days of the week. That throws the incrementing of the rows by 8 off one line further each time there is that row for the week day. Adjusting.....

9. Yes, the only thing I updated was taking out those rows. That made the very bottom rows work, but if all zero, then it gives that error. I even updated the total rows (I think) and that didn't fix it either. ??

M1:
=(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))

N1:
=(SUMPRODUCT((\$A\$5:\$A\$439="Total")*(\$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="Labor \$")*(\$D\$5:\$D\$439+\$I\$5:\$I\$439)))/SUMPRODUCT((\$A\$5:\$A\$439="Total")*(\$D\$5:\$D\$439+\$I\$5 :\$I\$439))

O1
=SUMPRODUCT((\$A\$5:\$A\$439="Total")*(\$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="Labor \$")*(\$D\$5:\$D\$439+\$I\$5:\$I\$439))

Steve

11. Hmm, Steve, that seams to actually work the best and without having to create a macro and hit an update button, although, that was pretty cool too.
Looks like it's all based on on the wording on the left as the key item? THANKS!

kzkz

I have completed the spreadsheet. Addressed problem with extra lines for the days of the week, eliminated cause of overflow error (Division by zero), and to make it more user friendly, I threw in some additional code to clear the contents of the input values (not formulas). Hope you are able to use it.

Maud

13. Saw your sheet. As an aside, you may find that making this a MS Access effort will add ease to this...as a lot of Excel reference to... or "+" links get lost, broken and "odd things" happen. Just an aside.

I agree that the sum product approach will work better as you have a lot of modular pieces here. If you want to get an average without using the AVERAGE function you need to /number of entries which I didn't see.... if you have 5 rows to be averaged, then it's rows/ number of rows .. COUNT(X..y) or COUNTA (X...Y)... number of rows/COUNT(Row 1 ... last row) for an average.

14. MQG1023, thanks for your input, but several different people will be using this form and they either don't have Access or don't have a clue how to use it. Regarding the formulas, which one are you saying (or neither) is incorrect?
Maud, thanks for the fix, not sure that I'd need the clear form button (I know how to delete it-but thanks for making that!), someone might hit it by accident and clear all the data. The manager will be completing one of these each week and want to actually have a new tab created each week and be able to look back in history. I'll keep one tab as a template that he'll just rename each week.
Steve, thanks for your formulas too. I like that, but something's actually wrong. If I put in say \$1000 to be invoiced for labor with no expenses at all, it calculates the parts percentage with a value.

15. I like that, but something's actually wrong. If I put in say \$1000 to be invoiced for labor with no expenses at all, it calculates the parts percentage with a value.
I don't understand the issue you raise. The formulas are weighted examples including all the results. Could you post an example , detailing the failing and which formulas fail and what the expected result is and the logic?

Steve
PS The % parts is from Cols of D &I. I t sums the of parts - sum of the rows with Cogs and divides that by the sum of parts. [(Parts- Cogs)/Parts

The % Profit is rows making: (Total - Cogs - Labor\$) / Total
The \$Profit is the rows containing: Total - Cogs - Labor\$

Page 2 of 3 First 123 Last

#### Posting Permissions

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