Rotor gauge ranking

[Turnipboy]

Rotor Mode Gauge 1 Gauge 2 Gauge 3
0 1 1 4 4
0 2 6 5 5
0 3 8 4 7
0 4 8 6 8
1 1 7 5 7
1 2 9 4 8
2 1 8 5 9
2 2 5 4 1
2 3 5 8 5

I have the above table in a spreadsheet. The first column shows the
rotor stage. For each rotor stage there are a number of modes of
vibration indicated in the second column. Each stage has gauges in
three positions, these positions have different allowable strains for a
given mode, on a given rotor stage, and these are indicated in the
3rd-5th columns. So for instance for rotor stage 2 vibrating at mode 1
the allowable strain at gauge position 3 is 9.

In another sheet (in the same book) I have this spreadsheet:
Rotor 0

Mode Gauge Highest recorded strain
1 1 7
2 6
3 5

2 3 9
2 8
1 6

3 2 8
1 7
3 1

This table is for rotor stage 0, there are also separate spreadsheets
for the other rotor stages that follow the same format. So I have found
the highest recorded strain at each gauge position for a given mode of
vibration. So for instance, the highest recorded strain for mode 3 at
gauge position 1 is 7. I would like to add another column in this sheet
that tells me if that gauge position is the best, 2nd best, worst, joint
best, joint 2nd best position. The better a gauge position, the higher
the allowable strain it has. So for instance for the example of gauge 1
for mode 3 (for rotor 0) this is the best gauge position (by seeing that
it has the highest allowable strain of all the gauges for that mode and
rotor stage in the first table i.e. it has an allowable strain of 8
which is better than 4 and 7).

How do I get excel to automatically tell me the second table if I am at
the best, worst etc gauge position for each row.
Max showed me how to look up the allowable strain for a given gauge
position, stage and mode:

http://www.excelforum.com/showthread.php?t=486632&highlight=turnipboy

The beauty of his method was that I could add information into the
first table e.g. extra modes, and the spreadsheet did not have a
problem with this. Could a similar thing be done here?

Thanks.

 

[Max]

Perhaps one way to try ..

Sample construct available at:
http://www.savefile.com/files/4324673
RotorGaugeRanking_Turnipboy_gen.xls

Assuming the "allowable strain" table is in sheet: X, in A1:E10
(data within A2:E10)

Rotor Mode Gauge 1 Gauge 2 Gauge 3
0 1 1 4 4
0 2 6 5 5
0 3 8 4 7

etc

and this is in sheet: Y, cols A to C, from row1 down

Rotor 0

Mode Gauge Highest recorded strain
1 1 7
.....................2 6
.....................3 5

etc

Enter the labels in D3:E3 : Allowable strain, Rank (1=best, 3=worst)

Put in D4, array-enter (i.e. press CTRL+SHIFT+ENTER):
=IF(B4="","",IF(ISNA(MATCH(1,(X!$A$2:$A$10=$B$1)*(X!$B$2:$B$10=N(INDIRECT("A
"&INT((ROW(A1)-1)/4)*4+4))),0)),"",INDEX(OFFSET(X!$A$2:$A$10,,MATCH($B$3&"
"&B4,X!$1:$1,0)-1),MATCH(1,(X!$A$2:$A$10=$B$1)*(X!$B$2:$B$10=N(INDIRECT("A"&
INT((ROW(A1)-1)/4)*4+4))),0))))

Put in E4:
=IF(D4="","",RANK(D4,OFFSET(INDIRECT("D"&INT((ROW(A1)-1)/4)*4+4),,,3)))

Select D4:E4, copy down to E14

Col D will return all the "allowable strain" readings from sheet: X
for Gauges 1-3, for the Modes 1-3 in col A and the Rotor number in B1

Col E returns the rankings within each mode (1=best, 3=worst)

Adapt the ranges in D4 to suit the actual extent of the data in sheet: X

--

 

[wjohnson]

Try useing the "LARGE" function.
=LARGE(B1:C3,1) this gives me the largest value of cells B1:C3
=LARGE(B1:C3,2) this gives me the 2nd largest value of the same cells.
=LARGE(B1:C3,3) this gives me the 3rd largest value of the same cells

 

[Turnipboy]

Wow, thanks again for your help.

I am trying to adapt the tips I have received to get the spreadsheet to
rank the gauge position even if there is no data from a particular gauge
for a specified mode and rotor stage (as is sometimes the case). I have
used Max's method that he previously showed me to put in the allowable
strain (I am still trying to work out the new method you have used!).
And I am trying to rank this allowable strain against the spreadsheet
with all the allowable strains, for the given mode and rotor stage
(i.e. the three values that correspond to that mode and stage).

 

[Max]

Is your response below for me? My offering wasn't a 100% solution, but
hopefully it brought you close enough for it to be acceptable <g>. I
couldn't get the final translation of the ranking numbers in col E (1=best,
3=worst) into the descriptives that you specified in your orig. post, viz.:
best, 2nd best, worst, joint best, joint 2nd best

Do hang around awhile for insights/alternatives from others to the above /
your orig. post

 

[Turnipboy]

Thanks.

My comments were based on your solution Max, but were directed at
anyone. I have got the spreadsheet to do what I want but it is a little
ugly. I have used the original formula you gave to index the allowable
strains for each gauge position at the particular mode and stage on the
same row (I have created some dummy/workings columns to the right that
are out of the way) and then used RANK to rank the given gauge against
these three values, this value goes into another 'dummy/workings'
column. I have than used an if statement to get a column to say what I
what e.g. best, worst etc based on this ranking.

 

[Max]

Thanks for clarifying !

 

[Turnipboy]

Anyone but Max especially (if you have the time and inclination), is it
possible to do this in a neater fashion. If the mode, rotor stage and
gauge position are given in a row in one spreadsheet is it possible to
rank the allowable strain for this situation against the 3 allowable
strains for that mode, and stage in the another spreadsheet. Can an
offset be used (that is not range specific) to create the ranking
list.

Spreadsheet 1
RS M P Rank
1 2 2 ?

? should come out to be 3 (can this be done more neatly than I did it)

Spreadsheet 2
RS M P1 P2 P3
0 1 3 5 6
0 2 5 6 7
1 1 4 5 1
1 2 3 2 4

 

[Max]

Here's another crack at it ..

Sample construct at:
http://www.savefile.com/files/9040915
RotorGaugeRanking_v2a_Turnipboy_gen.xls

In Sheet: X,
in cols A to E (the P's cols can be extended further
w/o having to edit the formula), data from row2 down

RS M P1 P2 P3
0 1 3 5 6
0 2 5 6 7
1 1 4 5 1
1 2 3 2 4

In sheet: Y,
RS's, M's, P's are in cols A to C,
inputs / data from row2 down

RS M P Rank
1 2 2 ?

Put in D2, array-enter the formula, i.e. press CTRL+SHIFT+ENTER:

=IF(COUNT(A2:C2)<3,"",
IF(OR(ISNA(MATCH(C$1&C2,X!$1:$1,0)),ISNA(MATCH(1,(X!$A$2:$A$10=A2)*(X!$B$2:$
B$10=B2),0))),"---",
RANK(INDEX(OFFSET(X!$A:$A,,MATCH(C$1&C2,X!$1:$1,0)-1),MATCH(1,(X!$A$2:$A$10=
A2)*(X!$B$2:$B$10=B2),0)+1),
OFFSET(X!A$1:C$1,MATCH(1,(X!$A$2:$A$10=A2)*(X!$B$2:$B$10=B2),0),MATCH(C$1&C2
,X!$1:$1,0)-2))))

Copy D2 down as far as required

Col D returns the required rankings
for the corresponding RS's, M's and P's in cols A to C

Error traps included in the formula:
Incomplete inputs in cols A to C return blanks: "",
non-matching cases return "---"

Adapt the ranges in the formula (eg: X!$A$2:$A$10) to suit

--

 

[Max]

non-matching cases return "---"


Adapt the error trap part in the formula to suit,
(if the "---" return is not desired)

 

[Max]

Oops, apologies, there was an error in the array formula in D2

Put instead in D2, array-enter the formula, i.e. press CTRL+SHIFT+ENTER:

=IF(COUNT(A2:C2)<3,"",
IF(OR(ISNA(MATCH(C$1&C2,X!$1:$1,0)),ISNA(MATCH(1,(X!$A$2:$A$10=A2)*(X!$B$2:$
B$10=B2),0))),"---",
RANK(INDEX(OFFSET(X!$A:$A,,MATCH(C$1&C2,X!$1:$1,0)-1),MATCH(1,(X!$A$2:$A$10=
A2)*(X!$B$2:$B$10=B2),0)+1),
OFFSET(X!A$1,MATCH(1,(X!$A$2:$A$10=A2)*(X!$B$2:$B$10=B2),0),2,,COUNTA(X!$1:$
1)-2))))

Here's the revised sample:
http://cjoint.com/?mEjniLxWEt
RotorGaugeRanking_v3_Turnipboy_gen.xls
(Sample in the previous link has been deleted)

Note that should we add-on more "P" cols to the right in sheet: X,
e.g: P4, P5 ...
the "ranking array" will auto-adjust to include all of the new "P" cols
(I've assumed this is the behaviour that you want)

 

[Max]

Use this sample instead (comments updated):
http://cjoint.com/?mEjQTfPfTE
RotorGaugeRanking_v3_Turnipboy_gen.xls

 

[Max]

In case you're back only after the earlier (cjoint) link expires <g>:
http://www.savefile.com/files/7910835
RotorGaugeRanking_v3_Turnipboy_gen.xls