CODEWITHSUNDEEP
excel
Cole
Cole

Reputation: 23

Multiple occurrences of MAX values in excel - select MAX value that has highest value in adjacent cell

I have a spreadsheet with weight lifting data that has:

  1. number of reps and
  2. total weight lifted (for those reps)

I want to reserve a section at the bottom of the spreedsheet to record personal bests. I want to do two things in that respect:

  1. find the heaviest weight that has been lifted
  2. for that identified heaviest weight, I want to know how many reps the weight was lifted

I have managed to do this by using MAX and OFFSET functions but here is the problem:

What I want excel to do is find the heaviest weight lifted for the most reps (i.e., if the second occurrence of the heaviest weight lifted was lifted for more reps than the first instance, I want that value reported). In my example this is 5 reps. 

|Rep  Weight |Rep  Weight |Rep  Weight |Rep  Weight |Rep  Weight |
| 6    120   | 8    120   | 10   120   | 4     140  | 5     140  |


 Personal best
|Rep   Weight |
| 5      140  |

Upvotes: 2

Views: 6463

Answers (2)

barry houdini
barry houdini

Reputation: 46331

OFFSET function is problematic when you use the whole row because you can't shift 1 cell right or left without referring to a cell that doesn't exist, hence #REF! error. Perhaps just use a large range without using the whole row, e.g. Excel 2003 has 256 columns so you can use every column but one, e.g. in C5 for the maximum weight

=MAX(IF(B1:IV1="Weight",B2:IV2))

and then in another cell for the associated best rep

=MAX(IF(B1:IV1="Weight",IF(B2:IV2=C5,A2:IU2)))

No need for OFFSET, you can just use different ranges......

both formulas confirmed with CTRL+SHIFT+ENTER

Upvotes: 1

Jüri Ruut
Jüri Ruut

Reputation: 2530

A1:J1 Headings "Max weight" and "Rep"
A2:J2 Values for Max weight and Rep interleaved

The heaviest weight that was lifted as an array formula:

{=MAX(IF(OFFSET(B2:K2;-1;0)="Max weight";B2:K2;""))}

The maximum reps the heaviest weight was lifted can be expressed by an array formula:

{=MAX(IF(OFFSET(A2:J2;-1;0)="Rep";IF(OFFSET(A2:J2;0;1)=C5;OFFSET(A2:J2;0;0);"");))}

The array formula is inserted by Ctrl-Shift-Enter from formula window. Curled brackets are inserted by Excel, not by a user.

enter image description here Example spreadsheet: http://www.bumpclub.ee/~jyri_r/Excel/The_best_result_from_weight_lifting.xls

Upvotes: 0

Related Questions