Forum Discussion
Best way to use maxifs combined with replacing values in "lookup array"
Hi, I am trying to find the best way to create a simple yet interesting formula in excel I have 4 arrays of values all the same size. The lookup array, a "replacement value array" and 2 arrays...
You could very likely us an Excel formula. It lacks Python's extensive libraries but, other that that, it is perfectly capable of carrying out calculation.
= LET(
modifiedArr, IF(lookupArr<0.1, replacementArr, lookupArr),
conditionalMax, MAX(IF(criterionArr, modifiedArr)),
conditionalMax
)
I like this solution except it isn't working for me for some reason. I think it is breaking down when giving the conditional statement to the 1st variable name
=LET( summarised,IF('150m_Results'!U5:U1084 < 0.1,'150m_Results'!BC5:BC1084,'150m_Results'!U5:U1084), WaveHcrit,'150m_Results'!C5:C1084,WavePcrit,'150m_Results'!D5:D1084,WaveDircrit,'150m_Results'!E5:E1084, MAXIFS(summarised,WaveHcrit,C5,WavePcrit,D5,WaveDircrit,">15"))All references 1 x 1080 arrays except "C5" and "D5" used in the maxifs function at the end
Check Peter's arrangement again. MAXIFS won't work because it will only accept ranges. You must use MAX(IF( arrangement.
alternatively just combine the conditionals:
=MAX( IF('150m_Results'!U5:U1084 < 0.1,'150m_Results'!BC5:BC1084,'150m_Results'!U5:U1084) *('150m_Results'!C5:C1084=C5) *('150m_Results'!D5:D1084=D5) *('150m_Results'!E5:E1084>15) )
