Heat Map

So are you having to find every instance of the Max value, look above & below each, comparing the output ? Presumably you don't always have 4 instances of the MAX value, and not all instances are necessarily adjacent to each other?

I think given the likely complexity you will face you will be best served utilising a UDF... now I had come up with this:

Please Login or Register  to view this content.

However I am encountering issues running the Find against your values which I suspect is down to the levels of precision at play in the matrix ... for ex. if I call the function directly from within VBA (to test) and alter the code such that I look for the Max value rounded to 4 decimals (as per your formatting in the matrix) it works, however, the same does not work when called directly from a cell. No doubt I'm missing / overlooking something obvious.

I will ask some others to review / resolve.

Hold off for time being as it's not working - I think I'm doing something stupid (more so than normal).

Essentially UDF's are customised Functions, they are generally stored within the file in which they are used - in VBA Modules... they are then "called" from a cell... eg

=MAXPW(D11:AC35,AF7,AI7,"P")

so the above runs the function MAXPW looking for the MAX value (AF7) in the Matrix of values (D11:AC35), once found it will use the number of data points as set in AI7 to establish the sum of data points in/around the max value... it will compare each range it finds (ie one range for each instance of the MAX) ... it will then return either the Percentile or Window associated with the Max range pending whether you say you want the P(ercentile) or W(indow) - as set by the final parameter in the function.

DO,
You can't use FindNext in a UDF. (In older versions of Excel IIRC, you can't even use Find) You may be better served loading the data into an array and looping through that?

No doubt I'm missing / overlooking something obvious.

Why did I not know that ?
(perhaps best not to answer...)

What is the advantage to loading the range into an Array for processing as opposed just iterating the Range direct ?

But you can use Find instead of FindNext within the loop.

OK so after making myself out to be a complete idiot (again) here is a revised version which iterates the matrix...

Please Login or Register  to view this content.

The above would sit within a Module... per your model and example:

AI9: =MAXPW($D$11:$AC$35,$AF$7,$AI$7,$AH9)
copied to AI10

Originally Posted by DonkeyOte

What is the advantage to loading the range into an Array for processing as opposed just iterating the Range direct ?

Quicker than reading cells one by one.