Is this a pivot table?

[Greymud]

I have a spreadsheet that has 3 columns
Machine Directive Hours

Machine is the name of the machine; Directive is PM type (daily, weekly, etc); Hours is the time recorded for each PM over the year. I'm looking to average the hours for each Machine by Directive to use this for future planning.

Is there a way to get a pivot table to give an average instead of a count? Most likely, I'm headed in the wrong direction with the pivot table.

Any help is appreciated.

 

[etaf]

when you see the count in the design view - where you drag the values into columns/rows/data etc
click on the arrow by the count hours
then you will see a choice, Sum , Average etc
choose average

 

[dUBBINS]

Honestly, I hate pivot tables personally. But if you didn't click on Create a Pivot Table, then it isn't one.

Okay, so I assume each machine has a number of Directives. You can just use a Sumifs formula. Assuming they are in Columns A, B, C:

=SUMIFS(C2:C5,A2:A5,"machine 1",B2:B5,"directive 1")

And just alter it to fit your data. Make one of these for each possible machine/directive. Or make it so it reads the cells instead. Does this make sense?

 

[Greymud]

This is perfect. I guess I was looking too hard at the problem to see the simple solution.

Thanks for the quick response!

 

[Greymud]

The pivot table modification from user etaf worked, but I'd like to learn your way, as well. Also, what is wrong with pivot tables? Is it just a limited function? Does it make the sheet less efficient or use a lot more resources? I only have 30k rows or so. Next year, we probably won't break 80k. I doubt we'll get over 150k ever based on factory size.

I used =averageifs to test how it works.

For the test:
Column A is Machine; Column B is Directive; Column C is hours recorded
I copied A2 and B2 to E2 and F2. In G2, I used
=AVERAGEIFS(C:C,A:A,E2,B:B,F2)

How do I find unique combinations of Column A + Column B programmatically? For example,
Machine A Daily
Machine A Weekly
Machine B Daily
Machine B Weekly

If I can get those values into Columns E and F, the rest is easy.

Also, sorry if I'm using incorrect terminology - I'm still very new at this.

 

[etaf]

i use pivot tables a lot and have since the early 90's for using as a simple tool to present summary information and provide analysis and charting
so I prefer to use pivot table if they are the right tool for the job

 

[dUBBINS]

@Greymud...I had posted that before I saw etaf had answered. I wouldn't have mentioned it had he already posted before I was typing. Anyway, no they are not inefficient, and if they are working for what you need then they are not limited. I guess I probably just like to build something custom fitted to what I am doing. That being said, that doesn't mean Pivot Tables are not the way to go. Just a personal preference of how to accomplish goals.

I guess I am not completely following your question. Are you wanting it to group all four of those examples (Machine A Dai....ne B Weekly) as being one unique combo? Or would you want it to find all, say, "Machine A Daily"? Or, say, all "Machine A", for both Daily and Weekly and group those?

No worries about terminology. I am always learning something new on here. And if you notice I have far less posts that etaf; so their advice is probably better anyway.

 

[Greymud]

@etaf
Thanks for the help - it worked perfectly.

@dUBBINS
I'm thinking I may learn something from this exersize that could come in handy later.

Columns A,B,C are my PMs for the year. For every Machine (A, B, C, etc), there are 300 Daily PMs, 52 weekly PMs, ... up to 12.5 year PMs when those happen.

In colums E and F, I'd like a list of unique combinations of Columns A and B. One Machine A Daily, One Machine Monthly, One Machine B Daily, etc. Then I can generate a list of

Machine A - Daily PM - Average time for year
Machine A - Weekly PM - Average time for year
Machine B - Daily PM - Average time for year
Machine B - Weekly PM - Average time for year
etc

I've already figured out how to do an average for this list in E and F, I just don't know how to make this list. Maybe it would be easier if I concatenate then find unique in one list instead of a combination of the two?