Jun 06 2019 05:07 AM - edited Jun 06 2019 05:08 AM
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.
Jun 06 2019 03:46 PM
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.
Jun 11 2019 11:34 PM
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?
Jun 12 2019 01:07 AM
Jun 12 2019 08:39 AM
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
Jun 12 2019 11:41 PM
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.
Jun 13 2019 03:15 AM
Jun 12 2019 08:39 AM
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