6 Replies Latest reply on Aug 8, 2016 11:45 AM by Benjamin Greene

# Calculating Area on a Heat Map

I've been struggling with this for a bit...

I'm trying to calculate the length and width of a heat map that exceeds a threshold.  In the attached workbook, I need to find:

1. For each letter, what is the starting number and ending number where the threshold is exceeded, the number of consecutive columns where the threshold is exceeded, AND how many non-consecutive instances there are of a threshold being exceeded.

Thus for "G", I'd get:

Instance Over Threshold      Start      End      Length

1                                             9          11          3

2                                             19         21         3

3                                            27          27          1

4                                             31          33          3

2. For each number, what s the starting letter and ending letter where the threshold is exceeded, the number of consecutive columns where the threshold is exceeded, AND how many non-consecutive instances there are of a threshold being exceeded.

Thus for 31 i'd get:

Instance Over Threshold      Start      End      Length

1                                             B          B          1

2                                             E          G          3

I'll need that data to build a summary on a separate worksheet. Any thoughts?  I've run this by a few people and keep getting stuck! Any help would be GREATLY appreciated!

• ###### 1. Re: Calculating Area on a Heat Map

Alright, amazing Community folks -- this one is a challenge for me, as well.  I would love to hear any and all suggestions the brilliant users of this Community can come up with!

• ###### 2. Re: Calculating Area on a Heat Map

I...I think I got it?

EDIT: Minor formatting updates in workbook.

3 of 3 people found this helpful
• ###### 3. Re: Calculating Area on a Heat Map

GENIUS!

Thank you.   I'm going to try this in my real data tonight (which is nested a bit deeper in the dimensions) to see if that impacts it...but this looks to be exactly what I was after.

THANK YOU!!!!!

1 of 1 people found this helpful
• ###### 4. Re: Calculating Area on a Heat Map

See... the power of the Community

• ###### 5. Re: Calculating Area on a Heat Map

I clearly remember the first time I answered a Forum question with a bunch of Table Calcs... such a glorious feeling.  Great job Benjamin Greene!

• ###### 6. Re: Calculating Area on a Heat Map

Happy to help! It was a fun challenge to work through.

For what it's worth, I am now realizing that (for the sake of limiting the number calculated fields) S (L) and S (N) could be eliminated by working their IF statements into the first argument of the WINDOW_MAX calculations for Start (L) and Start (N) directly, if you'd rather save that intermediate step.