Results 1 to 6 of 6
Thread: Various Formulas (Excel 2000)

20040203, 15:17 #1
 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.

20040203, 15:31 #2
 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

20040203, 16:52 #3
 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>

20040204, 14:31 #4
 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.

20040204, 15:33 #5
 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

20040204, 16:02 #6
 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