Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 45
  1. #16
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    661
    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.

  2. #17
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 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

  3. #18
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Quote Originally Posted by kzkz View Post
    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. #19
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,199
    Thanks
    45
    Thanked 228 Times in 211 Posts
    Please see my previous post revised with VB code and a updated spreadsheet with the code in place.

    Maud

  5. #20
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    661
    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.

  6. #21
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    661
    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 "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. #22
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,199
    Thanks
    45
    Thanked 228 Times in 211 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

  8. #23
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,199
    Thanks
    45
    Thanked 228 Times in 211 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.....

  9. #24
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    661
    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. ??

  10. #25
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 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

  11. #26
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    661
    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!

  12. #27
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,199
    Thanks
    45
    Thanked 228 Times in 211 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
    Attached Files Attached Files

  13. #28
    Star Lounger
    Join Date
    Jul 2012
    Posts
    99
    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.

  14. #29
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    661
    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 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. #30
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 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.
    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$
    Last edited by sdckapr; 2012-09-22 at 21:23.

Page 2 of 3 FirstFirst 123 LastLast

Posting Permissions

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