CODEWITHSUNDEEP
excelexcel-formula
tywtyw2002
tywtyw2002

Reputation: 167

In Excel, how can I get the maximum number with a condition?

In Excel, I use the Col A as the id number for my data, and the ID number begins at the year. Such as 2018xxxxxxx. I want to get the maximum id number for a year.

In Python, use a simple condition check, and then I can get the result. Such as.

col_A_max = max([x for x in range(A) if str(x) == '2016'])

I tried to rewrite it in Excel, but it did not work.

=max(if(A:A, left(A:A, 4) = "2016", A:A))

Upvotes: 0

Views: 127

Answers (2)

SJR
SJR

Reputation: 23081

Try this array formula:

=max(if(left(A:A, 4) = "2016", A:A))

Use Ctrl + Shift + Enter to confirm. (The first six characters will never be "2018".)

This might depend on whether you are using dates.

Upvotes: 1

VBasic2008
VBasic2008

Reputation: 54777

MAX Array Formula

The following are array formulas. After they are entered (copied) to the formula bar, hold LEFT CONTROL + SHIFT and press ENTER. (Enter them without the curly braces ({})).

{=MAX((LEFT(A:A,4)="2016")*A:A)}

{=MAX((LEFT(A$1:A$20,4)="2016")*A$1:A$20)}

The following image demonstrates how this formula will work if the values in the A column are either numbers or numbers formatted as text, while the other formulas will not. This principle cannot be applied to MIN.

enter image description here

Upvotes: 0

Related Questions