2

I have Google Sheets data in column pairs from Column B to AQ.

  1. Each column pair represents a single year with the first column containing order counts, Orders, and the second containing order totals in dollars, Total Amount.

  2. Each row of data from row 5 to row 16 represents a single month.
    For example, First three column-pairs (2010-2012):

    A B C D E F G
    3 YEAR 2010 2011 2012
    4 Orders Total Amount Orders Total Amount Orders Total Amount
    5 JAN 104 $65,920 102 $60,405 113 $50,557
    6 FEB 83 $70,426 107 $84,661 144 $109,721
    7 MAR 155 $108,184 108 $93,670 171 $96,946
    8 APRIL 173 $128,289 170 $112,715 202 $143,675
    9 MAY 147 $116,743 171 $106,875 187 $126,177
    10 JUNE 182 $125,567 189 $116,535 204 $105,806
    11 JULY 133 $101,204 157 $98,740 164 $93,466
    12 AUG 152 $109,569 223 $155,894 197 $131,516
    13 SEPT 206 $175,095 253 $175,384 221 $163,917
    14 OCT 170 $129,687 245 $129,948 217 $141,719
    15 NOV 152 $109,569 155 $99,301 141 $118,090
    16 DEC 128 $92,860 128 $84,728 150 $97,319
  3. Seperately for each of Orders, and Total Amount, I want to apply conditional formatting in order to:

    1. Highlight in green the cell with the largest number for the current month (row) across all years (columns)
    2. Highlight in yellow the cell with the largest number across all months (rows) and years (columns).
      For example, using bold for green and code formatting for yellow:
    A B C D E F G
    3 YEAR 2010 2011 2012
    4 Orders Total Amount Orders Total Amount Orders Total Amount
    5 JAN 104 $65,920 102 $60,405 113 $50,557
    6 FEB 83 $70,426 107 $84,661 144 $109,721
    7 MAR 155 $108,184 108 $93,670 171 $96,946
    8 APRIL 173 $128,289 170 $112,715 202 $143,675
    9 MAY 147 $116,743 171 $106,875 187 $126,177
    10 JUNE 182 $125,567 189 $116,535 204 $105,806
    11 JULY 133 $101,204 157 $98,740 164 $93,466
    12 AUG 152 $109,569 223 $155,894 197 $131,516
    13 SEPT 206 $175,095 253 $175,384 221 $163,917
    14 OCT 170 $129,687 245 $129,948 217 $141,719
    15 NOV 152 $109,569 155 $99,301 141 $118,090
    16 DEC 128 $92,860 128 $84,728 150 $97,319

I seem to have gotten it to work for "Total Amount" however, when I try to duplicate the same functionality for "Orders", it doesn't work.

C5=MAX($C$5:$C$16, $E$5:$E$16, $G$5:$G$16, $I$5:$I$16, $K$5:$K$16, 
       $M$5:$M$16, $O$5:$O$16, $Q$5:$Q$16, $S$5:$S$16, $U$5:$U$16, 
       $W$5:$W$16, $Y$5:$Y$16, $AA$5:$AA$16, $AC$5:$AC$16, 
       $AE$5:$AE$16, $AG$5:$AG$16, $AI$5:$AI$16, $AK$5:$AK$16, 
       $AM$5:$AM$16, $AO$5:$AO$16, $AQ$5:$AQ$16)

Link to my example spreadsheet

0

1 Answer 1

0
  1. Your current approach will always fail for one of your rules:
    • A cell can't have 2 background colors at once and since it's always the case that the cell with the maximum number for all months will also be the cell with the maximum number for its own month, only the first rule's background color will be applied. You need to exclude the overlapping use cases
  2. Your rules can be standardized using the approach below:

MAX By Month

ORDERS
--------------------
Range:   B5:AP16
Formula: =LET(array,FILTER($B$5:$AP$16,
            INDEX(MOD(SEQUENCE(1,COLUMNS($B$5:$AP$16)),2))),
              AND(B5=MAX(INDEX(array,ROW(B5)-ROW($B$5)+1)), B5<>MAX(array)))

TOTAL AMOUNTS
--------------------
Range:   C5:AQ16
Formula: =LET(array,FILTER($C$5:$AQ$16,
            INDEX(MOD(SEQUENCE(1,COLUMNS($C$5:$AQ$16)),2))),
              AND(C5=MAX(INDEX(array,ROW(C5)-ROW($C$5)+1)), C5<>MAX(array)))

MAX All Time

ORDERS
--------------------
Range:   B5:AP16
Formula: =LET(array,FILTER($B$5:$AP$16,
            INDEX(MOD(SEQUENCE(1,COLUMNS($B$5:$AP$16)),2))),
              AND(B5=MAX(INDEX(array,ROW(B5)-ROW($B$5)+1)), B5=MAX(array)))

TOTAL AMOUNTS
--------------------
Range:   C5:AQ16
Formula: =LET(array,FILTER($C$5:$AQ$16,
            INDEX(MOD(SEQUENCE(1,COLUMNS($C$5:$AQ$16)),2))),
              AND(C5=MAX(INDEX(array,ROW(C5)-ROW($C$5)+1)), C5=MAX(array)))

Explanation

  1. Regardless of the rule pair, for visual simplicity in my examples below:
  • column B is used instead of column [B|C]
  • column AP is used instead of column [AP|AQ]
  1. The rules in each pair, MaxByMonth and MaxAllTime, are written to be mutually exclusive so that rule order doesn't matter.
  2. In the rules, MaxAllTime =MAX(array), as a result, the rules in a pair differ only in whether x<>MAX(array), MaxByMonth, or x=MAX(array), MaxAllTime.
  3. The MaxAllTime formulas can actually be shortened as the MaxByMonth test that is included is redundant for that use case. However, I chose to keep them as near duplicates hoping that make them simpler to manage.
    MaxByMonth condition:
    B5=MAX(INDEX(array,ROW(B5)-ROW($B$5)+1))
  4. The FILTER function is used to return the array of values to be checked:
    FILTER($B$5:$AP$16,
      INDEX(MOD(SEQUENCE(1,COLUMNS($B$5:$AP$16)),2)))
    1. FILTER's range argument is a fixed range reference $ that matches the formatting rule's range argument:  
      $B$5:$AP$16
    2. FILTER's condition includes every other column in the range beginning with the first column:
      INDEX(MOD(SEQUENCE(1,COLUMNS($B$5:$AP$16)),2))
      1. First, an array of numbers is created using SEQUENCE starting from one up to the number of columns in the range:
        =SEQUENCE(1,COLUMNS($B$5:$AP$16))
        ={1;2;3;4;...;...;38;39;40;41}
      2. Then the MOD function (modulo) is used to return the remainder of dividing each number in the array by 2:
        =MOD({1;2;3;4;...;...;38;39;40;41},2)   // result from #5.2.1
        ={1;0;1;0;...;...;0;1;0,1}
      3. FILTER will accept an array of ones and zeros as a condition to indicate columns to keep, 1, and columns to discard, 0, mapping their position in the condition array to the same position in the range:
        =FILTER({x;y;x;y,...;...;y;x;y;x},   // example range 
                {1;0;1;0;...;...;0;1;0;1}    // condition (result from #5.2.2)
        ={x;x;...;x;x}
  5. Finally all that remains is to check if the value being tested is both "MaxByMonth and MaxAllTime" for one rule, or "MaxByMonth but not MaxAllTime" for the other rule.
    1. MaxByMonth is calculated applying MAX to the current month (row) of the filtered array returned in #5:

      MAX(INDEX(array,ROW(B5)-ROW($B$5)+1))
      1. INDEX is used to return the correct row from array
      2. INDEX's row argument is calculated by subtracting the row number of the first row in the original range ROW($B$5) from the row number belonging to the value currently being tested ROW(B [ 5-16 ]) and adding 1 to the result. The reason this is done is because INDEX's row argument is based on position in the array/range, not the original spreadsheet row number:
         range=B5:B16
        original return value row calculate value with INDEX index row index ------------------------------------------------- B5 INDEX(range,1)  1 ROW(B5)-ROW($B$5)+1 B6 INDEX(range,2)  2 ROW(B6)-ROW($B$5)+1 B7 INDEX(range,3)  3 ROW(B7)-ROW($B$5)+1   . . . .   . . . .   . . . . B14 INDEX(range,10)  10 ROW(B14)-ROW($B$5)+1 B15 INDEX(range,11)  11 ROW(B15)-ROW($B$5)+1 B16 INDEX(range,12)  12 ROW(B16)-ROW($B$5)+1
    2. MaxAllTime is simply MAX(array)

4
  • Ok, that did get it to start highlighting in yellow but it is still highlighting the wrong cells. Both rules are highlighting values in yellow that are not the all time max.
    – Wes B
    Commented Apr 18 at 18:03
  • I just checked your sheet and both MaxAllTime rules have the wrong ranges
    – Blindspots
    Commented Apr 18 at 20:43
  • 1
    Thank you so much for the help and specifically the explanation of the formula! I am fairly new to complex formulas but this worked great.
    – Wes B
    Commented Apr 19 at 13:36
  • My pleasure. There are some great questions and answers on this site. You'd be surprised how fast you can learn. Don't be shy about asking the community for help. See What can I do when someone answers my question?
    – Blindspots
    Commented Apr 19 at 15:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.