Forum Discussion

Copper Contributor

Jun 30, 2023

Solved

Max function for Spilled Range

Hello, I am struggling to resolve the following. With DA (Dynamic Array) formulas I calculate 3 columns: App: =IF(D7=1,INDEX(SORTBY($AF$15#,$AG$15#,-1),SEQUENCE(AJ11)),INDEX(SORTBY($AF$15#,$AG$1...

excel

Formulas and Functions

SergeiBaklan
Jun 30, 2023
Tegzi

As variant

=IF( B2# > C2#, B2#, C2#)

SergeiBaklan

Diamond Contributor

Jun 30, 2023

Tegzi

As variant

=IF( B2# > C2#, B2#, C2#)

Peter1979
Copper Contributor
Mar 08, 2024
SergeiBaklan Do we have any idea why MAX doesn't spill? Do we know which functions spill and which don't?
- SergeiBaklan
  Diamond Contributor
  Mar 09, 2024
  Peter1979
  
  If you mean to return max in each row in the array like
  
  when MAX() has no idea you'd like to max by row or by col, by default it returns max for entire range. We need to instruct Excel additionally would we like to have max value in each row or in each column or by some more complex logic. For that BYROW, BYCOL or like functions exist.
Tegzi
Copper Contributor
Jun 30, 2023
SergeiBaklan

Thanks. Indeed I did not even think that a simple if could handle the spilled range and DA function results...I guess I over complicated. Thanks for the solution.

Best regards,
Tegzi
- SergeiBaklan
  Diamond Contributor
  Jul 01, 2023
  Tegzi , you are welcome