SOLVED

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

Copper Contributor

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?
 
Capture.JPG
 
Thanks!
- Noortje
6 Replies

@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.

@Sergei Baklan 

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?

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).
best response confirmed by Noortje Vollenberg (Copper Contributor)
Solution

@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

 

@Sergei Baklan 

 

Hi Sergei,

 

Thanks so much for your help. This is working perfectly!

 

I've added a second variation of the formula with AND( .... ;U2=0) to hide the text for cells with a value of 0 so the file is more legible.

@Noortje Vollenberg 

 

I see, didn't think about all zeroes. 

1 best response

Accepted Solutions
best response confirmed by Noortje Vollenberg (Copper Contributor)
Solution

@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

 

View solution in original post