Average formula if different cell & column
- Thread starter stormania
- Start date
GraH - Guido
Well-Known Member
If you can add a helper column (or use a let statement?)
[H5] = DATE(2000+B5:B72;XMATCH(A5:A72;{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"});1)
[F5] =
=(SUM(($F$5:$F$72)*($D$5:$D$72=D5)*($H$5#>=EDATE(H5;-2))*($H$5#<EDATE(H5;0)))+
SUM(($G$5:$G$72)*($D$5:$D$72=D5)*($H$5#>=EDATE(H5;0))*($H$5#<EDATE(H5;3)))
)/(SUM(($D$5:$D$72=D5)*($H$5#>=EDATE(H5;-2))*($H$5#<EDATE(H5;0)))
+SUM(($D$5:$D$72=D5)*($H$5#>=EDATE(H5;0))*($H$5#<EDATE(H5;3))))
Aweful, but it seems to do the job.
There is some redundancy, so that can be avoided by playing with the "+" and putting it in the correct order, since that's an or statement. But I don't have a fresh mind due to a lack of sleep to figure it out.
[H5] = DATE(2000+B5:B72;XMATCH(A5:A72;{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"});1)
[F5] =
=(SUM(($F$5:$F$72)*($D$5:$D$72=D5)*($H$5#>=EDATE(H5;-2))*($H$5#<EDATE(H5;0)))+
SUM(($G$5:$G$72)*($D$5:$D$72=D5)*($H$5#>=EDATE(H5;0))*($H$5#<EDATE(H5;3)))
)/(SUM(($D$5:$D$72=D5)*($H$5#>=EDATE(H5;-2))*($H$5#<EDATE(H5;0)))
+SUM(($D$5:$D$72=D5)*($H$5#>=EDATE(H5;0))*($H$5#<EDATE(H5;3))))
Aweful, but it seems to do the job.
There is some redundancy, so that can be avoided by playing with the "+" and putting it in the correct order, since that's an or statement. But I don't have a fresh mind due to a lack of sleep to figure it out.