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

	A​	B​	C​	D​	E​	I​	K​	P​
7​	Date​	Day​	Month​	Week​	Exercise​	A W​	Up W​	Deload​
13​	29-May-17​	Mon​	May​	1​	Shoulder Press​	45​	S​
14​	29-May-17​	Mon​	May​	1​	Shoulder Press​	55​	S​
15​	29-May-17​	Mon​	May​	1​	Shoulder Press​	65​	S​
16​	29-May-17​	Mon​	May​	1​	Shoulder Press​	75​	S​
17​	29-May-17​	Mon​	May​	1​	Shoulder Press​	85​	U​
41​	5-Jun-17​	Mon​	Jun​	2​	Shoulder Press​	50​	S​
42​	5-Jun-17​	Mon​	Jun​	2​	Shoulder Press​	60​	S​
43​	5-Jun-17​	Mon​	Jun​	2​	Shoulder Press​	70​	S​
44​	5-Jun-17​	Mon​	Jun​	2​	Shoulder Press​	80​	S​
45​	5-Jun-17​	Mon​	Jun​	2​	Shoulder Press​	90​	U​
69​	12-Jun-17​	Mon​	Jun​	3​	Shoulder Press​	55​	S​
70​	12-Jun-17​	Mon​	Jun​	3​	Shoulder Press​	65​	S​
71​	12-Jun-17​	Mon​	Jun​	3​	Shoulder Press​	75​	S​
72​	12-Jun-17​	Mon​	Jun​	3​	Shoulder Press​	85​	S​
73​	12-Jun-17​	Mon​	Jun​	3​	Shoulder Press​	95​	U​
97​	20-Jun-17​	Tue​	Jun​	4​	Shoulder Press​	55​	S​
98​	20-Jun-17​	Tue​	Jun​	4​	Shoulder Press​	65​	S​
99​	20-Jun-17​	Tue​	Jun​	4​	Shoulder Press​	75​	S​
100​	20-Jun-17​	Tue​	Jun​	4​	Shoulder Press​	85​	S​
101​	20-Jun-17​	Tue​	Jun​	4​	Shoulder Press​	95​	U​
125​	27-Jun-17​	Tue​	Jun​	5​	Shoulder Press​	60​	S​
126​	27-Jun-17​	Tue​	Jun​	5​	Shoulder Press​	70​	S​
127​	27-Jun-17​	Tue​	Jun​	5​	Shoulder Press​	80​	S​
128​	27-Jun-17​	Tue​	Jun​	5​	Shoulder Press​	90​	S​
129​	27-Jun-17​	Tue​	Jun​	5​	Shoulder Press​	100​	D​	90​

<tbody>
</tbody>

Sheet: Ramped_5x5

<tbody>
</tbody>

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

	B​	C​	D​
1​	Exercise​	UP W​	Result​
2​	Shoulder Press​	U​	95​

<tbody>
</tbody>

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

	B​	C​	D​
1​	Exercise​	UP W​	Result​
2​	Shoulder Press​	D​	90​

<tbody>
</tbody>

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

	A​	B​
1​	Exercise​	Top Set​
2​	Bench Press	155​
3​	Deadlift	175​
4​	Shoulder Press	95​
5​	Squat	180​

<tbody>
</tbody>

Sheet: Ramped_5x5

<tbody>
</tbody>

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.