Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    Ontario, Canada
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Various Formulas (Excel 2000)

    Hi,

    I have a factory production report for 2 factories on the attached spreadsheet.

    It has relatively straightforward calcultions. I need help with a couple of production analysis formulas which is for both plants. This is the max and min units.

    Also, I need help with the salary analysis to compute the total wages with overtime.

    I need a sanity check for the avg. units producted per hour. I cell formatted with 0 decimal places. Is that correct or should it be rounded?

    All the data is included.

    Many thanks.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Various Formulas (Excel 2000)

    Whether you round the average (D23) to zero decimals or just display as 0 is up to you. It only makes a difference if you are using it later in calcs. Rounding will change the value used in calcs while displaying it differently will not.

    The max( D24) is:
    <pre>=MAX(E9:E14,J9:J14)</pre>


    The min (D25) is:
    <pre>=MIN(E9:E14,J9:J14)</pre>


    The total wages rexdale (I23) is
    <pre>=B15*I20+C15*I21</pre>


    The total wages concord (I24) is
    <pre>=G15*I20+H15*I21</pre>


    Steve

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Various Formulas (Excel 2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>Jay

    OK I took a look at your workbook, and I found some things that I would have done, so I hope you like them.

    1) "This is the max and min units" OK check the Max and Min functions. These are where you put them in cells D24 and D25.

    2) "total wages with overtime" Is this not <font color=blue># of Regular Hours worked * Rate of pay </font color=blue>+<font color=red> # of Overtime Hours worked * Overtime Rate of pay</font color=red>?

    See what I have done in cells I23 and I24 and I added the Total wages figure just for the heck of it <img src=/S/cool.gif border=0 alt=cool width=15 height=15> in cell I25.

    3) "avg. units producted per hour" You got that right, in cell D22. I guess it is the number of units produced divided by the # of hours to produce them.

    4) "I cell formatted with 0 decimal places. Is that correct or should it be rounded" Jay its your workbook, some things should be rounded, but others should not. I would suggest you check the workbook I am attaching to see where I left the rounding off.

    All the underlined cells have formulas, so I did that for you to look at. You may want to remove this format when you are done checking the formulas.

    I also added a section in Columns A and B at Row Row 40 to make a section for use with the cells E24, E25 and D26. I though you would like to see these extras as well.

    OK hope this gave you a starting point. Great work, you have done a wonderful job.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  4. #4
    New Lounger
    Join Date
    Mar 2002
    Location
    Ontario, Canada
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Various Formulas (Excel 2000)

    Thanks Steve & Wassim.

    I like the few additions you made Wassim and this works well. The only thing that had to be corrected was the max/min you had. It was to be a combined check which Steve's formula had correctly nailed.

    However, for the VLOOKUP formula in D24 generates an error #N/A (should be Thursday), but the similar formula in D25 works.??

    I've attached the updated sheet.


    Thanks Again.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Various Formulas (Excel 2000)

    The name templist is defined as:

    <pre>='Production Group Weekly Report'!$A$41:$C$48
    </pre>


    The VLOOKUP in cell E24 is looking for the value 631,590. That value is found in cell A52, which is outside the range defined by templist. Since the last parameter to VLOOKUP is FALSE, it is looking for an exact match which it does not find in the range A41:A48. That is causing the #N/A error. Just changing the definition of templist to include A52 also will not produce the result you seek since the cell at C52 is blank, not a date. My best guess is that the formula you want in E24 is:

    <pre>=VLOOKUP(D24,$B$41:$C$48,2,FALSE)
    </pre>


    If you want to use a range name for the table, you will need to define a new name for this range.
    Legare Coleman

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Various Formulas (Excel 2000)

    You can expand Templist to be:
    ='Production Group Weekly Report'!$A$41:$C$54

    And then in C49 enter:
    =A9
    and copy C49 to c50:C54

    and then E24 and E25 should not get an error no matter where the min/max is.

    Steve

Posting Permissions

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