Forum Discussion
Using Excel lookup based on multiple criteria
Hi David,
please see attached for the sample solution. For the second highest rep I used the following formula (array entered):
{=INDEX($E$2:$E$500,LARGE(IF($C$2:$C$500=K1,ROW($C$2:$C$500)-ROW(INDEX($C$2:$C$500,1,1))+1),2))}
Similarily, for the second highest weight I used
{=INDEX($F$2:$F$500,LARGE(IF($C$2:$C$500=K1,ROW($C$2:$C$500)-ROW(INDEX($C$2:$C$500,1,1))+1),2))}
Hope this helps
Regards
Yury
Hi David,
In general everything is possible. Your sample is empty except errors in the cells. If you provide some sample with numbers and where you'd like to have which result that will be easier - bit hard to recover the logic of your model without the knowledge of what's that about.
Thank you
Hi Sergei,
I have updated the spreadsheet to provide more details.
TABS:
Tracking - Data that will be recording from my workouts
1RM - Calculator for 1RM based on the number in `Notes`
Notes - This is where the fomulas are to get the highes rep, second hights rep and weights from my very first post. I used the fomulas from Yuri to get this all working. Happy to is it working with your formulas.
I was curious if it was possible to do it horizontaly vs vertially to save having to type the exercise 4 - 5 for each workout. Seemed more logical with approach. Open to any other suggestion, i am trying to simplfy the repetiviness. I still have not work out a way from not have to type week 1, week 2 etc and the date the exercise was done, could do a macro bust seems like overkill to me.
Hi David,
Not sure about formulas with rows/columns, i'm back to AGGREGATE. The idea is to take instead of single column the range from D to M columns and apply filter to use every first/second column, like for Highest weight
=AGGREGATE(14,6,1/(Tracking!$C$3:$C$20='1RM'!$C$1)*Tracking!$D$3:$M$20*{0,1,0,1,0,1,0,1,0,1},1)Had no time to play with other calculations, shall be similar. Please see the yellow cell in attached.
Hi David,
I updated the rest of formulas with how i understood that logic
Set Formula Highest Rep =INDEX(Tracking!$A$3:$M$20,MATCH('1RM'!$C$1,Tracking!$C$3:$C$20,0),MATCH(G20,OFFSET(Tracking!$A$3,MATCH('1RM'!$C$1,Tracking!$C$3:$C$20,0)-1,0,1,13),0)-1) Second Highest Rep =INDEX(Tracking!$A$3:$M$20,MATCH('1RM'!$C$1,Tracking!$C$3:$C$20,0),MATCH(G21,OFFSET(Tracking!$A$3,MATCH('1RM'!$C$1,Tracking!$C$3:$C$20,0)-1,0,1,13),0)-1) Rep Difference =IF(G16>G15,0,G15-G16) Weight Difference =IF(G21>G20,0,G20-G21) KG per Rep =IF(HighestRep-SecondHighestRep=0,WeightDifference/1,IF(G17<=0,WeightDifference/(SecondHighestRep-HighestRep),G18/G17)) Highest Weight =AGGREGATE(14,6,1/(Tracking!$C$3:$C$20='1RM'!$C$1)*Tracking!$D$3:$M$20*{0,1,0,1,0,1,0,1,0,1},1) Second Higest Weight =AGGREGATE(14,6,1/(Tracking!$C$3:$C$20='1RM'!$C$1)*Tracking!$D$3:$M$20*{0,1,0,1,0,1,0,1,0,1},2)Please note, in 1RM i took cell C1 to find the record (not B1 with incorrect list of values). No one array formula above.
File is attached
Sergei,
I found an issues that I was hoping you could help correct. What i want to do is find the highest rep and weight total and second highest rep and weight total.For example, currently the Highest Rep and Weight is showing:
Highest rep: 6
Highest Weight: 65
Second Highest rep: 8
Second Highest Weight: 55What it should be is:
Highest rep: 10
Highest Weight: 50
Second Highest rep: 8
Second Highest Weight: 5510 * 50 = 500
8*55 = 440From there the 1RM is calculated in the Notes sheet based on the results. Sorry to be a pain but would this be difficult to correct?
I have updated the spreadsheet in hope to provide greater detail as to what i am trying to do, changes made on the Tracking sheet for simplification. Eventually this data would be moved to the notes sheet.