NESTED FORMULAS WERE LOST WHEN I HAD TO ATTACH AN .XLS FILE BUT THE FORMULAS ARE SPELLED OUT.'
I have attached a sample of the the spreadsheet I inherited from an Excel user. I must recreate the formulas in columns M, N, and O by using an Access 2007 expression. Can someone help me? The Excel formula for
cell M2 is
=IF(MONTH(Period0)=1,P2,IF(MONTH(Period0)=2,SUM(P2:Q2),IF(MONTH(Period0)=3,SUM(P2:R2),IF(MONTH(Period0)=4,SUM(P2:S2),IF(MONTH(Period0)=5,SUM(P2:T2),IF(MONTH(Period0)=6,SUM(P2:U2),IF(MONTH(Period0)=7,SUM(P2:V2),IF(MONTH(Period0)=8,SUM(P2:W2),IF(MONTH(Period0)=9,SUM(P2:X2),IF(MONTH(Period0)=10,SUM(P2:Y2),IF(MONTH(Period0)=11,SUM(P2:Z2),SUM(P2:AA2))))))))))))
cell N2
=IF(MONTH(Period0)=1,A2,IF(MONTH(Period0)=2,SUM(A2:B2),IF(MONTH(Period0)=3,SUM(A2:C2),IF(MONTH(Period0)=4,SUM(A22),IF(MONTH(Period0)=5,SUM(A2:E2),IF(MONTH(Period0)=6,SUM(A2:F2),IF(MONTH(Period0)=7,SUM(A2:G2),IF(MONTH(Period0)=8,SUM(A2:H2),IF(MONTH(Period0)=9,SUM(A2:I2),IF(MONTH(Period0)=10,SUM(A2:J2),IF(MONTH(Period0)=11,SUM(A2:K2),SUM(A2:L2))))))))))))
cell O2
=SUM(A2:L2)
I have attached a sample of the the spreadsheet I inherited from an Excel user. I must recreate the formulas in columns M, N, and O by using an Access 2007 expression. Can someone help me? The Excel formula for
cell M2 is
=IF(MONTH(Period0)=1,P2,IF(MONTH(Period0)=2,SUM(P2:Q2),IF(MONTH(Period0)=3,SUM(P2:R2),IF(MONTH(Period0)=4,SUM(P2:S2),IF(MONTH(Period0)=5,SUM(P2:T2),IF(MONTH(Period0)=6,SUM(P2:U2),IF(MONTH(Period0)=7,SUM(P2:V2),IF(MONTH(Period0)=8,SUM(P2:W2),IF(MONTH(Period0)=9,SUM(P2:X2),IF(MONTH(Period0)=10,SUM(P2:Y2),IF(MONTH(Period0)=11,SUM(P2:Z2),SUM(P2:AA2))))))))))))
cell N2
=IF(MONTH(Period0)=1,A2,IF(MONTH(Period0)=2,SUM(A2:B2),IF(MONTH(Period0)=3,SUM(A2:C2),IF(MONTH(Period0)=4,SUM(A22),IF(MONTH(Period0)=5,SUM(A2:E2),IF(MONTH(Period0)=6,SUM(A2:F2),IF(MONTH(Period0)=7,SUM(A2:G2),IF(MONTH(Period0)=8,SUM(A2:H2),IF(MONTH(Period0)=9,SUM(A2:I2),IF(MONTH(Period0)=10,SUM(A2:J2),IF(MONTH(Period0)=11,SUM(A2:K2),SUM(A2:L2))))))))))))
cell O2
=SUM(A2:L2)
Attachments
Last edited: