Results 16 to 30 of 45
Thread: Get average of percentages

20120922, 11:47 #16
 Join Date
 May 2001
 Location
 Allen, Texas, USA
 Posts
 723
 Thanks
 1
 Thanked 0 Times in 0 Posts
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.

20120922, 12:32 #17
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20120922, 12:37 #18

20120922, 13:34 #19
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,394
 Thanks
 103
 Thanked 577 Times in 526 Posts
Please see my previous post revised with VB code and a updated spreadsheet with the code in place.
Maud

20120922, 15:18 #20
 Join Date
 May 2001
 Location
 Allen, Texas, USA
 Posts
 723
 Thanks
 1
 Thanked 0 Times in 0 Posts
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.

20120922, 15:48 #21
 Join Date
 May 2001
 Location
 Allen, Texas, USA
 Posts
 723
 Thanks
 1
 Thanked 0 Times in 0 Posts
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 "runtime 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

20120922, 16:02 #22
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,394
 Thanks
 103
 Thanked 577 Times in 526 Posts
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

20120922, 16:18 #23
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,394
 Thanks
 103
 Thanked 577 Times in 526 Posts
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.....

20120922, 16:44 #24
 Join Date
 May 2001
 Location
 Allen, Texas, USA
 Posts
 723
 Thanks
 1
 Thanked 0 Times in 0 Posts
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. ??

20120922, 17:48 #25
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
How about these formulas:
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

20120922, 18:19 #26
 Join Date
 May 2001
 Location
 Allen, Texas, USA
 Posts
 723
 Thanks
 1
 Thanked 0 Times in 0 Posts
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!

20120922, 18:21 #27
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,394
 Thanks
 103
 Thanked 577 Times in 526 Posts
Completed Spreadsheet
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

20120922, 18:43 #28
 Join Date
 Jul 2012
 Posts
 108
 Thanks
 0
 Thanked 4 Times in 4 Posts
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.

20120922, 20:47 #29
 Join Date
 May 2001
 Location
 Allen, Texas, USA
 Posts
 723
 Thanks
 1
 Thanked 0 Times in 0 Posts
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 itbut 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.

20120922, 21:14 #30
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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.
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$Last edited by sdckapr; 20120922 at 21:23.