Forum Discussion
Check condition repeatedly
I have two sheets named "Avg" and "Data" in one workbook. B column of Avg contains month and year. and B column of Data contains date in dd-mmm-yyyy format. I have to write a formula for C column of ...
if they are corresponding columns you should be able to use:
=IF(EOMONTH(B2,0)=EOMONTH(Avg!B2,0), [do calculation], "")
and fill down (I don't believe EOMONTH() works well with dynamic arrays)
alternatively you could use:
=IF(DATE(YEAR(B1:B100),MONTH(B1:B100),1)=Avg!B1:B100, [do calculation], "")
but that assumes the month year is truly month year (i.e. day is always 1) and change B1:B100 to the appropriate rows you need to check. Alternatively i highly recommend changing the ranges to be Formatted as a Table and then use the Table references.
=IF(EOMONTH(B2,0)=EOMONTH(Avg!B2,0), [do calculation], "")
and fill down (I don't believe EOMONTH() works well with dynamic arrays)
alternatively you could use:
=IF(DATE(YEAR(B1:B100),MONTH(B1:B100),1)=Avg!B1:B100, [do calculation], "")
but that assumes the month year is truly month year (i.e. day is always 1) and change B1:B100 to the appropriate rows you need to check. Alternatively i highly recommend changing the ranges to be Formatted as a Table and then use the Table references.