Hi Guys
Can anyone help please
I have this formula:
=IF(AND(EOMONTH($S23,0)=(EOMONTH(AW$2,0)),$O23="One-Off"),$AB23,IF(AND(EOMONTH(AW$2,0)>=(EOMONTH($S23,0)),EOMONTH(AW$2,0)<=(EOMONTH($T23,0)),($S23=DATE(YEAR(AW$2),MONTH(AW$2),1))),$AC23,IF(EOMONTH($S23,0)=EOMONTH(AW$2,0),$AB23/$U23*(EOMONTH(AW$2,0)-$S23),IF(EOMONTH($T23,0)=EOMONTH(AW$2,0),$AB23-SUM($AJ23:AV23),IF(AND(EOMONTH(AW$2,0)>=(EOMONTH($S23,0)),EOMONTH(AW$2,0)<=(EOMONTH($T23,0))),IF(EOMONTH($S23,0)=EOMONTH(AW$2,0),$AB23/$U23*(EOMONTH(AW$2,0)-$S23),IF(EOMONTH($T23,0)=EOMONTH(AW$2,0),$AB23-SUM($AJ23:AV23),$AC23)),0)))))
Basically I want to say this:
Where month column and cell text is "One-Off", pick up AB23,
=IF(AND(EOMONTH($S23,0)=(EOMONTH(AW$2,0)),$O23="One-Off"),$AB23,
But then I want it to say zero if false.
But then if it is not "One-Off" do this:
IF(AND(EOMONTH(AW$2,0)>=(EOMONTH($S23,0)),EOMONTH(AW$2,0)<=(EOMONTH($T23,0)),($S23=DATE(YEAR(AW$2),MONTH(AW$2),1))),$AC23,IF(EOMONTH($S23,0)=EOMONTH(AW$2,0),$AB23/$U23*(EOMONTH(AW$2,0)-$S23),IF(EOMONTH($T23,0)=EOMONTH(AW$2,0),$AB23-SUM($AJ23:AV23),IF(AND(EOMONTH(AW$2,0)>=(EOMONTH($S23,0)),EOMONTH(AW$2,0)<=(EOMONTH($T23,0))),IF(EOMONTH($S23,0)=EOMONTH(AW$2,0),$AB23/$U23*(EOMONTH(AW$2,0)-$S23),IF(EOMONTH($T23,0)=EOMONTH(AW$2,0),$AB23-SUM($AJ23:AV23),$AC23)),0)))))
I realise I have two false criterias and its probably my nested logic, but I would really appreciate some help from the experts on here.
Thanks
CD
Bookmarks