Viewing a single comment thread. View all comments

NickEcommerce t1_j6sa318 wrote

I have a bunch of rows that each contain a product name, and then the number of items sold for each of the last 12 months. How can I highlight which months are above average for the row? In excel that kind of conditional formatting gets applied to the entire dataset but each row needs it's own average calculation. I could apply a fresh conditional format to each row, but with more than 1,000 rows it's a big pain in the backside!

1

crimeo t1_j6ysk0j wrote

Not really a proper answer, but a loophole/workaround:

  • Make another copy of the whole table, but this time each row normalized (subtract minimum from the row then divide by (maximum - minimum)) so every row now goes 0 to 1.

  • Apply a single conditional format to the entire thing, since now each row is apples to apples and you only need one

  • Use this to visually navigate instead or to sort, and the left table to see the raw numbers

2

NickEcommerce t1_j711z9i wrote

Thats a great idea, thank you! Some of my numbers are so vast in range it didn't occur to me to normalise. They're sales figures so in a poor month an item might sell 1, but in a good month it might sell 250, so when figuring out seasonality I am finding it tough to pick out some "winning" months for a given product.

1