CODEWITHSUNDEEP
excelexcel-formula
LVFToENlCx
LVFToENlCx

Reputation: 875

MAX IF over column not recognizing values

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

Upvotes: 0

Views: 27

Answers (1)

girlvsdata
girlvsdata

Reputation: 1644

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))

Upvotes: 1

Related Questions