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