MAX IF over column not recognizing values

0
votes

I am trying to make an excel formula which will find the maximum value in a range of a column (P) where adjacent value in Q is equal to 2 minus value adjacent to the cell with the formula. For example, if I had

 p | q | r
 2 | 4 | formula
 3 | 6 | formula
 4 | 8 | formula
 5 | 8 | formula
 6 | 10| formula

I would want the formula to make it

 p | q | r
 2 | 4 | 0
 3 | 6 | 2
 4 | 8 | 3
 5 | 8 | 3
 6 | 10| 4

Currently I have 

=MAX(IF(Q:Q=(Q5-2),P$2:P5))

The numbers in Q are generated using the formula below, which counts the number of spaces in cells in a different column and is working as intended.

=FIND(LEFT(TRIM(A5),1),A5)-1
1
excelexcel-formula
which version of excel are you using? – girlvsdata
@girlvsdata excel 2011 on mac. The file in question is a CSV, if it is relevant – Sam Craig
what is your formula in Col R currently returning? – girlvsdata
@girlvsdata column R is returning 0 – Sam Craig

1 Answers

1
votes

The formula you've given is an array, it needs to be closed with CTRL+SHIFT+ENTER

Column Q's range also needs to be the same size as Column P's.

Put this in R2 and instead of ENTER, press CTRL+SHIFT+ENTER to run the formula. When it works the formula will be enclosed in curly brackets.

=MAX(IF($Q$2:$Q$6=(Q2-2),$P$2:$P$6))