Forum Discussion
Conditional formatting with custom formula: revenue value in last three months
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
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.
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).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
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.