Hi! I've got a custom formula for conditional formatting I'd like some help with. This URL holds a copy example of my original file: https://docs.google.com/spreadsheets/d/1wp3AMnlZr0hgRBLbQlMBgHe-a-g3VQOeFiCEgVZaUwU/edit?usp=sharing. It's a file with revenue data per customer per month. What I need is to highlight the last three months if there is no revenue or revenue under 200 EUR per month. I've got the part covered on how to highlight the last three months with the following formula:=AND(MONTH(U$1)<MONTH(TODAY());MONTH(U$1)>MONTH(TODAY())-4) To check the revenue I added ;SUM(U2:W2)<350 to the formula, but this isn't right. As the capture shows, in row 2 it highlights the 0 EUR - even though this is obviously an active customer. Then in row 4, the months of April and May aren't highlighted even though they should be.. I also tried ;U2<200 but this too highlights individual cells and not the combination of the last threemonths. Any ideas on how I can get this part of the formula to work? Thanks!- Noortje

Noortje Vollenberg , formula rule could be =((SUMPRODUCT( (($U$1:$AF$1<EOMONTH(TODAY(),-1))*($U$1:$AF$1>EOMONTH(TODAY(),-4)))*$U2:$AF2)<350)+(SUMPRODUCT( (($U$1:$AF$1<EOMONTH(TODAY(),-1))*($U$1:$AF$1>EOMONTH(TODAY(),-4)))*($U2:$AF2<200))=3))*(U$1<EOMONTH(TODAY(),-1))*(U$1>EOMONTH(TODAY(),-4)) Please check attached

Forum Discussion

Noortje Vollenberg

Copper Contributor

Jun 06, 2019

Solved

Conditional formatting with custom formula: revenue value in last three months

Hi!

I've got a custom formula for conditional formatting I'd like some help with. This URL holds a copy example of my original file: https://docs.google.com/spreadsheets/d/1wp3AMnlZr0hgRBLbQlMBgHe-a-g3VQOeFiCEgVZaUwU/edit?usp=sharing.

It's a file with revenue data per customer per month. What I need is to highlight the last three months if there is no revenue or revenue under 200 EUR per month.

I've got the part covered on how to highlight the last three months with the following formula:

=AND(MONTH(U$1)<MONTH(TODAY());MONTH(U$1)>MONTH(TODAY())-4)

To check the revenue I added ;SUM(U2:W2)<350 to the formula, but this isn't right. As the capture shows, in row 2 it highlights the 0 EUR - even though this is obviously an active customer. Then in row 4, the months of April and May aren't highlighted even though they should be.. I also tried ;U2<200 but this too highlights individual cells and not the combination of the last three

months.

Any ideas on how I can get this part of the formula to work?

Thanks!

- Noortje

excel

Formulas and Functions

SergeiBaklan

Jun 12, 2019

Noortje Vollenberg , formula rule could be

=((SUMPRODUCT( (($U$1:$AF$1<EOMONTH(TODAY(),-1))*($U$1:$AF$1>EOMONTH(TODAY(),-4)))*$U2:$AF2)<350)+(SUMPRODUCT( (($U$1:$AF$1<EOMONTH(TODAY(),-1))*($U$1:$AF$1>EOMONTH(TODAY(),-4)))*($U2:$AF2<200))=3))*(U$1<EOMONTH(TODAY(),-1))*(U$1>EOMONTH(TODAY(),-4))

Please check attached

Latest 3 months conditional formatting.xlsx138 KB

6 Replies

SergeiBaklan
Diamond Contributor
Jun 06, 2019
Noortje Vollenberg

Hi Noortje,

If you mean to check if this year revenue is less from 350, i.e. from Jan to running month, when you have to fix Jan in formula by absolute reference:

=AND(MONTH(U$1)<MONTH(TODAY()),MONTH(U$1)>MONTH(TODAY())-4,SUM($U2:W2)<350)

Please check in attached file.
Latest 3 months conditional formatting.xlsx137 KB
- Noortje Vollenberg
  Copper Contributor
  Jun 12, 2019
  SergeiBaklan
  Hi Sergei,
  
  Thanks for your response! What I'm looking for is actually not fixed (i.e. revenue from Jan - now), but should adapt to always include the last three months (i.e. revenue from March - May at this point in time).
  
  Meaning: at this point in time it should highlight March - May revenue if it is less than 350. If it were currently November, it should highlight August - October revenue if less than 350 combined (or <200 per month).
  
  Is this possible?
  - Noortje Vollenberg
    Copper Contributor
    Jun 12, 2019
    Perhaps this illustrates better what I am looking for:
    
    It's currently June, meaning that the 3 month period I'm looking at is March to May. I need these three months to be highlighted if every one of these months'revenue is below 200, or the combined revenue is below 350 (whichever works better in a formula).
    
    So if March=0, April=500, May=10 then this shouldn't be highlighted (the revenue in April shows this is an active customer).
    
    But if March=10, April=120, May=30 then these three months should be highlighted (this revenue does not show an active customer as the values are too low for significant purchases).