Pivot Tables, Linked Values 1

[YoungTurk]

I have a table of data (see attached example), from which I'd like to extract the maximum load at each attachment location. This is relatively easily done with a pivot table. However, I'd like to list the associated load condition for the maximum load, which I can't seem to figure out. A "robust" solution is required, since I'd like to extend this as part of an existing spreadsheet used by multiple users. Ideas?

 

[COEngineeer]

explain what maximum load you need. There are different condition. I am still confused what you are trying to find.

Never, but never question engineer's judgement

 

[jghrist]

You can use a MATCH() formula and an INDEX() formula to find the load condition for the maximum load.

See attached.

 

[YoungTurk]

jghrist,

I can make that concept work for my application, thank you!

I'm still interested in whether it is possible to accomplish this from within a pivot table...

COE,

I need/needed to find the maximum load for a given attachment location and direction and the associated load condition. For example, the maximum Py load for attachment 1 is 162.5 lbs at load condition 3.

 

[magoo2]

I tried a pivot - just for Py load.

Is this what you had in mind?

 

[YoungTurk]

Thanks for the try, but not quite. Your pivot essentially reiterates the summary in the original table, where I was looking to get only the maximum load for a given attachment location and direction, and also spit out the associated load condition.

Basically so it would tell the user "The maximum Py on attachment 1 is 162.5 at condition 3", etc., in a nice tabular format.

The purpose being to prevent errors ocurring when users manually pick out maximum loads for caculations, and also to allow subsequent automated calculations based on the maximum loads.

 

[magoo2]

I guess I don't understand what you're after.

You have a data set with 11 attachments and 5 load conditions. I laid it out by rows of results by attachment, load. So, (1,1) means you got attachment 1 and load 1, etc.

I thought you were trying to arrange the data in an orderly fashion. Is there more and different data for the same conditions? I didn't use a max function for the Py results since you only had 1 set of results for each condition. A sum works fine with what you sent.

Can you unconfuse me? I'd like to help.

 

[YoungTurk]

I must not be saying what I want very clearly, but I promise I am trying

For a given attachment, lets say attachment 1, I want to find the maximum load for a given direction, lets say Py. This can be done, among other ways, with a max function in the pivot table. Now, that given maximum load came from a certain load condition (only one load condition will have the maximum load for a given mount and direction). I would like to have the pivot table give me the condition associated with the maximum.

jghrist gave a perfectly acceptable way to do it, which I'm working from. Looking at his sheet may help clarify my question. I am still interested in whether it is possible to accomplish this from within the pivot table. This would be useful to me for further processing.