CODEWITHSUNDEEP
excel
Dennis
Dennis

Reputation: 13

Excel max number in range

I have a column A with many numbers (that are from 2000000 to 4999999). In cell K3 I want to have the maximum number of this column in the range of 280030 and 289999

Upvotes: 0

Views: 92

Answers (3)

Harun24hr
Harun24hr

Reputation: 37125

Use this formula: Press Ctrl+Shift+Enter as it is an array formula.

=MAX(IF(A:A>=280030,IF(A:A<=289999,A:A)))

Screenshot:

enter image description here

Upvotes: 0

Dennis
Dennis

Reputation: 13

D1011 is my min value and E1012 is my max value

=MAX(WENN((B4:B999>D1011)*(B4:B999<E1012);B4:B999))

That works!

Upvotes: 0

Xzila
Xzila

Reputation: 237

ASSUMPTION:

Assuming the set of numbers is between 2,000,000 to 4,999,999 & the conditional Max needs to return the highest number between 2,800,300 and 2,899,999 ( Instead of 280,030 and 289,999 )

EXCEL WITH HELPER COLUMN ( NOT ARRAY ):

  1. If you are using excel you can use a helper column to convert all numbers of the larger set to zero that do not conform to the criteria of being between 2,800,300 and 2,899,999. I accomplished this with a formula and copied it down =IF(AND(A1>2800300,A1<2899999),A1,) such that the helper column has an output for every number in Column A.

  2. If my helper column is in Column B, you can use the max formula =MAX(B:B) to return the conditional max.

GOOGLE SHEETS WITH ARRAY ( NOT HELPER COLUMN ):

  • Simply use the filter array to remove all cells of column A that are less than 2,800,300 or greater than 2,899,999, and take the max: =MAX(FILTER(A:A,A:A>2800300,A:A<2899999))

Upvotes: 1

Related Questions