Nested Max If Array Formula

[vulcan20mmsp]

Looking for a way to use array formula with false criteria or a better way to create a formula to achieve desired outcome.

So, if Exercise = shoulder press and Up W = U then find max of AW else use number in Deload column

My array formula worked will until I added an additional criteria to the mix.

Original formula was Max(if(e8:e162="shoulder press",if(k8:k162="U",i8:i162))) using ctrl+shift+enter. Worked well to find the max number.

I tried:

Max(if(e8:e162="shoulder press",if(k8:k162="U",i8:i162,k8:k162="D", p8162)))

Sample data below:

Excel 2016 (Mac) 64 bit

[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH][/TH]
[TH]

A​

[/TH]
[TH]

B​

[/TH]
[TH]

C​

[/TH]
[TH]

D​

[/TH]
[TH]

E​

[/TH]
[TH]

I​

[/TH]
[TH]

K​

[/TH]
[TH]

P​

[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

7​

[/TD]
[TD="bgcolor: #4472C4"]

Date​

[/TD]
[TD="bgcolor: #4472C4"]

Day​

[/TD]
[TD="bgcolor: #4472C4"]

Month​

[/TD]
[TD="bgcolor: #4472C4"]

Week​

[/TD]
[TD="bgcolor: #4472C4"]

Exercise​

[/TD]
[TD="bgcolor: #4472C4"]

A W​

[/TD]
[TD="bgcolor: #4472C4"]

Up W​

[/TD]
[TD="bgcolor: #4472C4"]

Deload​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

13​

[/TD]
[TD="bgcolor: #B4C6E7"]

29-May-17​

[/TD]
[TD="bgcolor: #B4C6E7"]

Mon​

[/TD]
[TD="bgcolor: #B4C6E7"]

May​

[/TD]
[TD="bgcolor: #B4C6E7"]

1​

[/TD]
[TD="bgcolor: #B4C6E7"]

Shoulder Press​

[/TD]
[TD="bgcolor: #B4C6E7"]

45​

[/TD]
[TD="bgcolor: #B4C6E7"]

S​

[/TD]
[TD="bgcolor: #B4C6E7"][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

14​

[/TD]
[TD="bgcolor: #D9E1F2"]

29-May-17​

[/TD]
[TD="bgcolor: #D9E1F2"]

Mon​

[/TD]
[TD="bgcolor: #D9E1F2"]

May​

[/TD]
[TD="bgcolor: #D9E1F2"]

1​

[/TD]
[TD="bgcolor: #D9E1F2"]

Shoulder Press​

[/TD]
[TD="bgcolor: #D9E1F2"]

55​

[/TD]
[TD="bgcolor: #D9E1F2"]

S​

[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

15​

[/TD]
[TD="bgcolor: #B4C6E7"]

29-May-17​

[/TD]
[TD="bgcolor: #B4C6E7"]

Mon​

[/TD]
[TD="bgcolor: #B4C6E7"]

May​

[/TD]
[TD="bgcolor: #B4C6E7"]

1​

[/TD]
[TD="bgcolor: #B4C6E7"]

Shoulder Press​

[/TD]
[TD="bgcolor: #B4C6E7"]

65​

[/TD]
[TD="bgcolor: #B4C6E7"]

S​

[/TD]
[TD="bgcolor: #B4C6E7"][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

16​

[/TD]
[TD="bgcolor: #D9E1F2"]

29-May-17​

[/TD]
[TD="bgcolor: #D9E1F2"]

Mon​

[/TD]
[TD="bgcolor: #D9E1F2"]

May​

[/TD]
[TD="bgcolor: #D9E1F2"]

1​

[/TD]
[TD="bgcolor: #D9E1F2"]

Shoulder Press​

[/TD]
[TD="bgcolor: #D9E1F2"]

75​

[/TD]
[TD="bgcolor: #D9E1F2"]

S​

[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

17​

[/TD]
[TD="bgcolor: #B4C6E7"]

29-May-17​

[/TD]
[TD="bgcolor: #B4C6E7"]

Mon​

[/TD]
[TD="bgcolor: #B4C6E7"]

May​

[/TD]
[TD="bgcolor: #B4C6E7"]

1​

[/TD]
[TD="bgcolor: #B4C6E7"]

Shoulder Press​

[/TD]
[TD="bgcolor: #B4C6E7"]

85​

[/TD]
[TD="bgcolor: #B4C6E7"]

U​

[/TD]
[TD="bgcolor: #B4C6E7"][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

41​

[/TD]
[TD="bgcolor: #D9E1F2"]

5-Jun-17​

[/TD]
[TD="bgcolor: #D9E1F2"]

Mon​

[/TD]
[TD="bgcolor: #D9E1F2"]

Jun​

[/TD]
[TD="bgcolor: #D9E1F2"]

2​

[/TD]
[TD="bgcolor: #D9E1F2"]

Shoulder Press​

[/TD]
[TD="bgcolor: #D9E1F2"]

50​

[/TD]
[TD="bgcolor: #D9E1F2"]

S​

[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

42​

[/TD]
[TD="bgcolor: #B4C6E7"]

5-Jun-17​

[/TD]
[TD="bgcolor: #B4C6E7"]

Mon​

[/TD]
[TD="bgcolor: #B4C6E7"]

Jun​

[/TD]
[TD="bgcolor: #B4C6E7"]

2​

[/TD]
[TD="bgcolor: #B4C6E7"]

Shoulder Press​

[/TD]
[TD="bgcolor: #B4C6E7"]

60​

[/TD]
[TD="bgcolor: #B4C6E7"]

S​

[/TD]
[TD="bgcolor: #B4C6E7"][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

43​

[/TD]
[TD="bgcolor: #D9E1F2"]

5-Jun-17​

[/TD]
[TD="bgcolor: #D9E1F2"]

Mon​

[/TD]
[TD="bgcolor: #D9E1F2"]

Jun​

[/TD]
[TD="bgcolor: #D9E1F2"]

2​

[/TD]
[TD="bgcolor: #D9E1F2"]

Shoulder Press​

[/TD]
[TD="bgcolor: #D9E1F2"]

70​

[/TD]
[TD="bgcolor: #D9E1F2"]

S​

[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

44​

[/TD]
[TD="bgcolor: #B4C6E7"]

5-Jun-17​

[/TD]
[TD="bgcolor: #B4C6E7"]

Mon​

[/TD]
[TD="bgcolor: #B4C6E7"]

Jun​

[/TD]
[TD="bgcolor: #B4C6E7"]

2​

[/TD]
[TD="bgcolor: #B4C6E7"]

Shoulder Press​

[/TD]
[TD="bgcolor: #B4C6E7"]

80​

[/TD]
[TD="bgcolor: #B4C6E7"]

S​

[/TD]
[TD="bgcolor: #B4C6E7"][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

45​

[/TD]
[TD="bgcolor: #D9E1F2"]

5-Jun-17​

[/TD]
[TD="bgcolor: #D9E1F2"]

Mon​

[/TD]
[TD="bgcolor: #D9E1F2"]

Jun​

[/TD]
[TD="bgcolor: #D9E1F2"]

2​

[/TD]
[TD="bgcolor: #D9E1F2"]

Shoulder Press​

[/TD]
[TD="bgcolor: #D9E1F2"]

90​

[/TD]
[TD="bgcolor: #D9E1F2"]

U​

[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

69​

[/TD]
[TD="bgcolor: #B4C6E7"]

12-Jun-17​

[/TD]
[TD="bgcolor: #B4C6E7"]

Mon​

[/TD]
[TD="bgcolor: #B4C6E7"]

Jun​

[/TD]
[TD="bgcolor: #B4C6E7"]

3​

[/TD]
[TD="bgcolor: #B4C6E7"]

Shoulder Press​

[/TD]
[TD="bgcolor: #B4C6E7"]

55​

[/TD]
[TD="bgcolor: #B4C6E7"]

S​

[/TD]
[TD="bgcolor: #B4C6E7"][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

70​

[/TD]
[TD="bgcolor: #D9E1F2"]

12-Jun-17​

[/TD]
[TD="bgcolor: #D9E1F2"]

Mon​

[/TD]
[TD="bgcolor: #D9E1F2"]

Jun​

[/TD]
[TD="bgcolor: #D9E1F2"]

3​

[/TD]
[TD="bgcolor: #D9E1F2"]

Shoulder Press​

[/TD]
[TD="bgcolor: #D9E1F2"]

65​

[/TD]
[TD="bgcolor: #D9E1F2"]

S​

[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

71​

[/TD]
[TD="bgcolor: #B4C6E7"]

12-Jun-17​

[/TD]
[TD="bgcolor: #B4C6E7"]

Mon​

[/TD]
[TD="bgcolor: #B4C6E7"]

Jun​

[/TD]
[TD="bgcolor: #B4C6E7"]

3​

[/TD]
[TD="bgcolor: #B4C6E7"]

Shoulder Press​

[/TD]
[TD="bgcolor: #B4C6E7"]

75​

[/TD]
[TD="bgcolor: #B4C6E7"]

S​

[/TD]
[TD="bgcolor: #B4C6E7"][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

72​

[/TD]
[TD="bgcolor: #D9E1F2"]

12-Jun-17​

[/TD]
[TD="bgcolor: #D9E1F2"]

Mon​

[/TD]
[TD="bgcolor: #D9E1F2"]

Jun​

[/TD]
[TD="bgcolor: #D9E1F2"]

3​

[/TD]
[TD="bgcolor: #D9E1F2"]

Shoulder Press​

[/TD]
[TD="bgcolor: #D9E1F2"]

85​

[/TD]
[TD="bgcolor: #D9E1F2"]

S​

[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

73​

[/TD]
[TD="bgcolor: #B4C6E7"]

12-Jun-17​

[/TD]
[TD="bgcolor: #B4C6E7"]

Mon​

[/TD]
[TD="bgcolor: #B4C6E7"]

Jun​

[/TD]
[TD="bgcolor: #B4C6E7"]

3​

[/TD]
[TD="bgcolor: #B4C6E7"]

Shoulder Press​

[/TD]
[TD="bgcolor: #B4C6E7"]

95​

[/TD]
[TD="bgcolor: #B4C6E7"]

U​

[/TD]
[TD="bgcolor: #B4C6E7"][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

97​

[/TD]
[TD="bgcolor: #D9E1F2"]

20-Jun-17​

[/TD]
[TD="bgcolor: #D9E1F2"]

Tue​

[/TD]
[TD="bgcolor: #D9E1F2"]

Jun​

[/TD]
[TD="bgcolor: #D9E1F2"]

4​

[/TD]
[TD="bgcolor: #D9E1F2"]

Shoulder Press​

[/TD]
[TD="bgcolor: #D9E1F2"]

55​

[/TD]
[TD="bgcolor: #D9E1F2"]

S​

[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

98​

[/TD]
[TD="bgcolor: #B4C6E7"]

20-Jun-17​

[/TD]
[TD="bgcolor: #B4C6E7"]

Tue​

[/TD]
[TD="bgcolor: #B4C6E7"]

Jun​

[/TD]
[TD="bgcolor: #B4C6E7"]

4​

[/TD]
[TD="bgcolor: #B4C6E7"]

Shoulder Press​

[/TD]
[TD="bgcolor: #B4C6E7"]

65​

[/TD]
[TD="bgcolor: #B4C6E7"]

S​

[/TD]
[TD="bgcolor: #B4C6E7"][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

99​

[/TD]
[TD="bgcolor: #D9E1F2"]

20-Jun-17​

[/TD]
[TD="bgcolor: #D9E1F2"]

Tue​

[/TD]
[TD="bgcolor: #D9E1F2"]

Jun​

[/TD]
[TD="bgcolor: #D9E1F2"]

4​

[/TD]
[TD="bgcolor: #D9E1F2"]

Shoulder Press​

[/TD]
[TD="bgcolor: #D9E1F2"]

75​

[/TD]
[TD="bgcolor: #D9E1F2"]

S​

[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

100​

[/TD]
[TD="bgcolor: #B4C6E7"]

20-Jun-17​

[/TD]
[TD="bgcolor: #B4C6E7"]

Tue​

[/TD]
[TD="bgcolor: #B4C6E7"]

Jun​

[/TD]
[TD="bgcolor: #B4C6E7"]

4​

[/TD]
[TD="bgcolor: #B4C6E7"]

Shoulder Press​

[/TD]
[TD="bgcolor: #B4C6E7"]

85​

[/TD]
[TD="bgcolor: #B4C6E7"]

S​

[/TD]
[TD="bgcolor: #B4C6E7"][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

101​

[/TD]
[TD="bgcolor: #D9E1F2"]

20-Jun-17​

[/TD]
[TD="bgcolor: #D9E1F2"]

Tue​

[/TD]
[TD="bgcolor: #D9E1F2"]

Jun​

[/TD]
[TD="bgcolor: #D9E1F2"]

4​

[/TD]
[TD="bgcolor: #D9E1F2"]

Shoulder Press​

[/TD]
[TD="bgcolor: #D9E1F2"]

95​

[/TD]
[TD="bgcolor: #D9E1F2"]

U​

[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

125​

[/TD]
[TD="bgcolor: #B4C6E7"]

27-Jun-17​

[/TD]
[TD="bgcolor: #B4C6E7"]

Tue​

[/TD]
[TD="bgcolor: #B4C6E7"]

Jun​

[/TD]
[TD="bgcolor: #B4C6E7"]

5​

[/TD]
[TD="bgcolor: #B4C6E7"]

Shoulder Press​

[/TD]
[TD="bgcolor: #B4C6E7"]

60​

[/TD]
[TD="bgcolor: #B4C6E7"]

S​

[/TD]
[TD="bgcolor: #B4C6E7"][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

126​

[/TD]
[TD="bgcolor: #D9E1F2"]

27-Jun-17​

[/TD]
[TD="bgcolor: #D9E1F2"]

Tue​

[/TD]
[TD="bgcolor: #D9E1F2"]

Jun​

[/TD]
[TD="bgcolor: #D9E1F2"]

5​

[/TD]
[TD="bgcolor: #D9E1F2"]

Shoulder Press​

[/TD]
[TD="bgcolor: #D9E1F2"]

70​

[/TD]
[TD="bgcolor: #D9E1F2"]

S​

[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

127​

[/TD]
[TD="bgcolor: #B4C6E7"]

27-Jun-17​

[/TD]
[TD="bgcolor: #B4C6E7"]

Tue​

[/TD]
[TD="bgcolor: #B4C6E7"]

Jun​

[/TD]
[TD="bgcolor: #B4C6E7"]

5​

[/TD]
[TD="bgcolor: #B4C6E7"]

Shoulder Press​

[/TD]
[TD="bgcolor: #B4C6E7"]

80​

[/TD]
[TD="bgcolor: #B4C6E7"]

S​

[/TD]
[TD="bgcolor: #B4C6E7"][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

128​

[/TD]
[TD="bgcolor: #D9E1F2"]

27-Jun-17​

[/TD]
[TD="bgcolor: #D9E1F2"]

Tue​

[/TD]
[TD="bgcolor: #D9E1F2"]

Jun​

[/TD]
[TD="bgcolor: #D9E1F2"]

5​

[/TD]
[TD="bgcolor: #D9E1F2"]

Shoulder Press​

[/TD]
[TD="bgcolor: #D9E1F2"]

90​

[/TD]
[TD="bgcolor: #D9E1F2"]

S​

[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

129​

[/TD]
[TD="bgcolor: #B4C6E7"]

27-Jun-17​

[/TD]
[TD="bgcolor: #B4C6E7"]

Tue​

[/TD]
[TD="bgcolor: #B4C6E7"]

Jun​

[/TD]
[TD="bgcolor: #B4C6E7"]

5​

[/TD]
[TD="bgcolor: #B4C6E7"]

Shoulder Press​

[/TD]
[TD="bgcolor: #B4C6E7"]

100​

[/TD]
[TD="bgcolor: #B4C6E7"]

D​

[/TD]
[TD="bgcolor: #B4C6E7"]

90​

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Ramped_5x5[/TD]
[/TR]
</tbody>[/TABLE]

I am using Excel for Mac 2016.

Thanks for any assistance.

 

[kalikj]

You need the following array formula (assuming your table starts in cell A1): {=MAX(IF(E2:E26="Shoulder Press",IF(G2:G26="U",F2:F26),H26))}

This assumes that the "Deload" value will be a single value, in a single reference cell per your example.

 

[vulcan20mmsp]

You need the following array formula (assuming your table starts in cell A1): {=MAX(IF(E2:E26="Shoulder Press",IF(G2:G26="U",F2:F26),H26))}

This assumes that the "Deload" value will be a single value, in a single reference cell per your example.

kalikj, thanks for the reply.

I tried below ( I entered the cell references I am using on sheet):

{=MAX(IF(E8:E162="Shoulder Press",IF(K8:K162="U",I8:I162),P129))}

Works fine if K8:K162 = U - if any other entry other than U finds the previous Max of K8:K162, in this case 95 because entry doesn't = U (95 is the previous Max entry). The formula doesn't pull the entry from P129.

K8:K162 can have one of three entries - U, S, or D. If U find Max of K8:K162, if D pull entry from P129.

Seems the formula ignores the false value.

 

[Marcelo Branco]

It's not clear for me what you are looking for.

I didn't understand what you mean by
"So, if Exercise = shoulder press and Up W = U then find max of AW else use number in Deload column"

Could you provide an example?

M.

 

[vulcan20mmsp]

Marcelo,

Exercise is the Table Column heading where the exercise names are.

The Exercise names are in range E8:E162 (shoulder press, bench press, squat, chin up, etc)

Up W is the Table Column heading where one of the three criteria will are.

The Up W criteria are range K8:K162 (U, S, or D)

AW is the Table Column heading where the pounds lifted are.

The AW numbers are range I8:I162.

Deload is the Table Column where a number goes if Up W = D.

Deload is range P8:P129 and will only have a number in cell when Up W = D.

The array formula {=Max(if(E8:E162="Shoulder Press",if(K8:K162="U",I8:I162)))} works when using two criteria (U or S).

If I use three criteria (U, S, or D) and I want to look at a column P8:P162 when K8:K162 equals D I have no success.

I can use name ranges - E8:E162 is Exercise; K8:K162 is UpW; I8:I162 is AW; P8:P162 is Deload. I haven't named the ranges on my worksheet, but I can do that.

I hope my explanation helps.

Thanks for your reply.

 

[Marcelo Branco]

Try something like this


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Exercise​

[/TD]
[TD]

UP W​

[/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD="bgcolor: #D9D9D9"]

Shoulder Press​

[/TD]
[TD="bgcolor: #D9D9D9"]

U​

[/TD]
[TD]

95​

[/TD]
[/TR]
</tbody>[/TABLE]


Criteria in B2:C2 (gray area)

Array formula in D2
=MAX(IF(E8:E162=B2,IF(K8:K162=C2,IF(C2="D",P8:P162,I8:I62))))
Ctrl+Shift+Enter

Then change C2 to D and you get

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Exercise​

[/TD]
[TD]

UP W​

[/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD="bgcolor: #D9D9D9"]

Shoulder Press​

[/TD]
[TD="bgcolor: #D9D9D9"]

D​

[/TD]
[TD]

90​

[/TD]
[/TR]
</tbody>[/TABLE]


Hope this helps

M.

 

[vulcan20mmsp]

Marcelo,

Your suggestion worked well and I was even able to add to the formula to handle "S" criteria.

=IF(K8="s",SMALL($I$8:$I$162,COUNTIF($I$8:$I$162,"<"&I8)-1),MAX(IF($E$8:$E$162=E8,IF($K$8:$K$162=K8,IF(K8="d",

The key factor I forgot when working the formula is I want the formula in one cell.

Sample data below shows each exercise with the Top Set:
Excel 2016 (Mac) 64 bit

[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH][/TH]
[TH]

A​

[/TH]
[TH]

B​

[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

1​

[/TD]
[TD]

Exercise​

[/TD]
[TD]

Top Set​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

2​

[/TD]
[TD]Bench Press[/TD]
[TD]

155​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

3​

[/TD]
[TD]Deadlift[/TD]
[TD]

175​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

4​

[/TD]
[TD]Shoulder Press[/TD]
[TD]

95​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

5​

[/TD]
[TD]Squat[/TD]
[TD]

180​

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Ramped_5x5[/TD]
[/TR]
</tbody>[/TABLE]

The number under Top Set is based on one of the three criteria in the formula "S, U, or D" and the Exercise column.

Not sure if such a formula can one from one cell while looking at the necessary ranges.

Thanks for the assist.

 

[Marcelo Branco]

Sorry, i am a little confused.
Your formula above seems truncated.
Have you completely solved your problem or there is some new issue to be fixed?

M.

 

[vulcan20mmsp]

Formula did get cut off.

=IF(K8="s",SMALL($I$8:$I$162,COUNTIF($I$8:$I$162,"<"&I8)-1),MAX(IF($E$8:$E$162=E8,IF($K$8:$K$162=K8,IF(K8="d",P8:P162,I8:I62))))

I would like to have the formula work from one cell and reference a range of data. The formula, as is, works if using down a column, but I don't see how I could put the formula into a cell and look at a range of data.

Again, thanks for your help and patience.

Of course, the new idea might be a new topic.

 

[Marcelo Branco]

We were talking about MAX(IF(....)). Now i can see SMALL and a different formula.

I really don't understand what you mean by
"I would like to have the formula work from one cell and reference a range of data. The formula, as is, works if using down a column, but I don't see how I could put the formula into a cell and look at a range of data."

It seems a new question. To make things easier i think you should provide an example explaining exactly what you are trying to do - a new data sample, criteria, along with expected results.
Or, if you prefer, create a new Thread.

M.