CODEWITHSUNDEEP
excelexcel-formula
neutronhammer
neutronhammer

Reputation: 145

Using AGGREGATE function to extract the maximum value in an array throws up an error

This is what my "code" looks like :

=AGGREGATE(4;3;{4;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!})

All I want is, for the AGGREGATE function to, in this case, return "4". Instead I keep getting a #Value! error. Alternatively, can this be extracted using a combination of other functions? I would be grateful for any tips and/or suggestions.

This is what I'm trying to do; replace my TRUE values with a number and later extract that number {TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}/{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}*{4;14;18;28;40;55;57;64}

Upvotes: 1

Views: 143

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60324

When the argument is an array, and not a range, you have to use one of the array functions (#14 and higher, if I recall correctly):

=AGGREGATE(14,3,{4;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!},1)

Upvotes: 4

Related Questions