F
Fred Newton
From data that comes from a large Pivot I am building a spreadsheet of
information that feeds into a chart. I am trying to create a line
showing projected charges for the year, based on charges to date. I
have worked out the formula to do this for each successive month, up
to September, where it all stops, due to the limit on nested if's. I
have seen, in other articles, mention made of Named cells/formulae but
don't know how to use these. The formulae I have developed are below,
can someone give me a pointer as to what to do to allow the figures to
generate through to the end of the year.
Cells B18 (January) thru M18(December) contain either the monthly
charge or 0. The cell with the formula in is the charge/projected
charge through to the end of the year, or at least, that's what i'm
trying to achieve. The last one that works is in J(September), but
only by removing the Sum function and replacing it with B18+C18,
October fails, whether I remove the Sum or not, pointing at the 9th
(???) if.
Jan - =B18
Feb - =IF(C18<>0,C18,B18)
Mar - =IF(D18<>0,D18,(IF(C18<>0,((C18+B18)/2),B18)))
Apr - =IF(E18<>0,E18,(IF(D18<>0,((D18+C18+B18)/3),IF(C18<>0,((C18+B18)/2),B18))))
May - =IF(F18<>0,F18,(IF(E18<>0,(SUM(B18:E18)/4),IF(D18<>0,(SUM(B1818)/3),IF(C18<>0,(SUM(B18:C18)/2),B18)))))
Jun - =IF(G18<>0,G18,(IF(F18<>0,(SUM(B18:F18)/5),IF(E18<>0,(SUM(B18:E18)/4),IF(D18<>0,(SUM(B1818)/3),IF(C18<>0,(SUM(B18:C18)/2),B18))))))
Jul - =IF(H18<>0,H18,(IF(G18<>0,(SUM(B18:G18)/6),IF(F18<>0,(SUM(B18:F18)/5),IF(E18<>0,(SUM(B18:E18)/4),IF(D18<>0,(SUM(B1818)/3),IF(C18<>0,(SUM(B18:C18)/2),B18)))))))
Aug - =IF(I18<>0,I18,(IF(H18<>0,(SUM(B18:H18)/7),IF(G18<>0,(SUM(B18:G18)/6),IF(F18<>0,(SUM(B18:F18)/5),IF(E18<>0,(SUM(B18:E18)/4),IF(D18<>0,(SUM(B1818)/3),IF(C18<>0,(SUM(B18:C18)/2),B18))))))))
Sep - =IF(J18<>0,J18,(IF(I18<>0,(SUM(B18:I18)/8),IF(H18<>0,(SUM(B18:H18)/7),IF(G18<>0,(SUM(B18:G18)/6),IF(F18<>0,(SUM(B18:F18)/5),IF(E18<>0,(SUM(B18:E18)/4),IF(D18<>0,(SUM(B1818)/3),IF(C18<>0,((B18+C18)/2),B18)))))))))
Oct- =IF(K18<>0,K18,(IF(J18<>0,(Sum(B18:J18)/8),IF(I18<>0,(Sum(B18:I18)/8),IF(H18<>0,(Sum(B18:H18)/7),IF(G18<>0,(Sum(B18:G18)/6),IF(F18<>0,(Sum(B18:F18)/5),IF(E18<>0,(Sum(B18:E18)/4),IF(D18<>0,(Sum(B1818)/3),IF(C18<>0,((B18+C18)/2),B18))))
Regards
Fred Newton
Zurich Financial Services
information that feeds into a chart. I am trying to create a line
showing projected charges for the year, based on charges to date. I
have worked out the formula to do this for each successive month, up
to September, where it all stops, due to the limit on nested if's. I
have seen, in other articles, mention made of Named cells/formulae but
don't know how to use these. The formulae I have developed are below,
can someone give me a pointer as to what to do to allow the figures to
generate through to the end of the year.
Cells B18 (January) thru M18(December) contain either the monthly
charge or 0. The cell with the formula in is the charge/projected
charge through to the end of the year, or at least, that's what i'm
trying to achieve. The last one that works is in J(September), but
only by removing the Sum function and replacing it with B18+C18,
October fails, whether I remove the Sum or not, pointing at the 9th
(???) if.
Jan - =B18
Feb - =IF(C18<>0,C18,B18)
Mar - =IF(D18<>0,D18,(IF(C18<>0,((C18+B18)/2),B18)))
Apr - =IF(E18<>0,E18,(IF(D18<>0,((D18+C18+B18)/3),IF(C18<>0,((C18+B18)/2),B18))))
May - =IF(F18<>0,F18,(IF(E18<>0,(SUM(B18:E18)/4),IF(D18<>0,(SUM(B1818)/3),IF(C18<>0,(SUM(B18:C18)/2),B18)))))
Jun - =IF(G18<>0,G18,(IF(F18<>0,(SUM(B18:F18)/5),IF(E18<>0,(SUM(B18:E18)/4),IF(D18<>0,(SUM(B1818)/3),IF(C18<>0,(SUM(B18:C18)/2),B18))))))
Jul - =IF(H18<>0,H18,(IF(G18<>0,(SUM(B18:G18)/6),IF(F18<>0,(SUM(B18:F18)/5),IF(E18<>0,(SUM(B18:E18)/4),IF(D18<>0,(SUM(B1818)/3),IF(C18<>0,(SUM(B18:C18)/2),B18)))))))
Aug - =IF(I18<>0,I18,(IF(H18<>0,(SUM(B18:H18)/7),IF(G18<>0,(SUM(B18:G18)/6),IF(F18<>0,(SUM(B18:F18)/5),IF(E18<>0,(SUM(B18:E18)/4),IF(D18<>0,(SUM(B1818)/3),IF(C18<>0,(SUM(B18:C18)/2),B18))))))))
Sep - =IF(J18<>0,J18,(IF(I18<>0,(SUM(B18:I18)/8),IF(H18<>0,(SUM(B18:H18)/7),IF(G18<>0,(SUM(B18:G18)/6),IF(F18<>0,(SUM(B18:F18)/5),IF(E18<>0,(SUM(B18:E18)/4),IF(D18<>0,(SUM(B1818)/3),IF(C18<>0,((B18+C18)/2),B18)))))))))
Oct- =IF(K18<>0,K18,(IF(J18<>0,(Sum(B18:J18)/8),IF(I18<>0,(Sum(B18:I18)/8),IF(H18<>0,(Sum(B18:H18)/7),IF(G18<>0,(Sum(B18:G18)/6),IF(F18<>0,(Sum(B18:F18)/5),IF(E18<>0,(Sum(B18:E18)/4),IF(D18<>0,(Sum(B1818)/3),IF(C18<>0,((B18+C18)/2),B18))))
Regards
Fred Newton
Zurich Financial Services