create a formula to pull the person's name and the avg number of cartons

**desertdude1** · 12-05-2007, 09:31 AM

I am trying to create a formula to pull the person's name and the avg number of cartons sorted for the month from this spreadsheet.

I've attached a sample of the spreadsheet I created, you will see at the bottom I would like to see the person that had the highest avg for the month and the person with the lowest avg for the month.

This is just a simple spreadsheet to track productivity of my sortation employees.

Thanks for your help in advance

**Ron Coderre** · 12-05-2007, 02:19 PM

Perhaps a little clarification would help....

Highest/Lowest Average...per what? per day? per month?
What if each person works a different number of days or hours?
Is the average calculated base only on hours and days worked?
Do you want to just read the high/low numbers (however you calc them) from the column summaries?

It would help if you could zip an Excel sample of your file and post it.

**shg** · 12-05-2007, 02:27 PM

I think you'd be happier before you're done if you arranged your data in a classical database format, with one column each for date, employee name, cartons/day, hours, etc. It will make the fomulas simpler and more maintainable.

**desertdude1** · 12-05-2007, 03:22 PM

The spreadsheet is setup by business day and by person. I enter the number of cartons sorted per day and the number of hours worked for the day. This gives me the persons daily avg.

At the bottom of that person's column it totals the number of cartons and hours for the month and it gives me the avg for the month.

What I am trying to do is have a formula look at all of my employees monthly averages and put their name and avg next to the text at the bottom of the spreadsheet.

Highest Avg per hour
Lowest Avg per hour

I realize their avg will change everyday.

**Ron Coderre** · 12-05-2007, 03:58 PM

There are many common structural errors in that workbook!
For now....Let's just address the formulas you want.

Please Login or Register  to view this content.

Now....a few constructive comments about some of the bigger issues:
1) Never prepend spaces to text to move it to the right!
Most times you can:
Center the cell contents
or
Format the cell to indent a number of spaces
<format><cells><alignment tab>...set the indent.

2) Avoid merged cells whenever possible
...Instead, use Center Across Selection
(also from: <format><cells><alignment tab>...Horizontal:Center Across Selection )

3) Avoid leaving only a space " " in a cell. If you intended to erase that cell...Press the [DELETE] key.

Does that help?

**desertdude1** · 12-05-2007, 05:03 PM

Ron, it works it pulls the name of the sorter like I asked. Is there a way to add his monthly avg also?

Thanks so much for your help. That was a very long formula.

**Ron Coderre** · 12-05-2007, 05:59 PM

Before we even go there, let's fix some of the formula problems...OK?

First, the average cartons per hour for the month is incorrect. It's wrong to average the averages.

Please Login or Register  to view this content.

Your formula averages 100 and 10 for a Monthly Avg/Hr = 55
...Obviously, wrong.

The Monthly Avg/Hr
= Total_Ctns/Total_Hrs
= 1010/110
= 91.82

And I'd suggest you put the correct average ctns per hr and per month at the bottom of the columns and adjust the formula I posted to search the appropriate bottom rows.

Does that help?

**desertdude1** · 12-06-2007, 08:45 AM

Ron I made your suggested changes and I am attaching the spreadsheet with the new changes.

Now with your new formula showing the persons name of the highest number of cartons sorted I would like to add their monthly avg next to their name at the bottom.

I imagine it would be some type of lookup formula, can you help?

Thanks for all your help...

**Ron Coderre** · 12-06-2007, 09:15 AM

Thanks for posting the file.

FYI: I made some un-asked-for changes.
The changed cells are shaded obnoxious, bright, green.

1) I reformatted the column headings to Center-across-selection and I removed the extra padding spaces.

2) I replaced the summary formulas to the right of the data with SUMPRODUCT formulas.
(Note that one of them returns an error because one of the referenced cells contains a space. Delete that space and the corresponding formula will display a value.)

Oh, and...I added the formulas you actually asked for, Max and Min values, at the bottom.

The workbook still needs work, but it's in much better shape.

Post back if you have questions.

**desertdude1** · 12-06-2007, 11:29 AM

Ron it looks great thanks again for your help.

create a formula to pull the person's name and the avg number of cartons

LinkBack

Thread Tools

Display