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

    Calculating Area on a Heat Map

    Christie Clark

      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!