Projected Charges

[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

 

[Frank Kabel]

Hi Fred
try the following (assumption this is directly below row 18 in row 19)
=IF(B18<>0,B18,SUM($B$18:B$18)/SUMIF($B$18:B$18,"<>" & 0))
copy this to the right
This will put either the value of row 18 into the cell (if this value
is <>0) or will sum all values of the previous months devided by the
number of months with a value <>0

HTH
Frank

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 -

May -

Jun -

Jul -

=IF(H18<>0,H18,(IF(G18<>0,(SUM(B18:G18)/6),IF(F18<>0,(SUM(B18:F18)/5),I

Aug -

=IF(I18<>0,I18,(IF(H18<>0,(SUM(B18:H18)/7),IF(G18<>0,(SUM(B18:G18)/6),I

Sep -

=IF(J18<>0,J18,(IF(I18<>0,(SUM(B18:I18)/8),IF(H18<>0,(SUM(B18:H18)/7),I
F(G18<>0,(SUM(B18:G18)/6),IF(F18<>0,(SUM(B18:F18)/5),IF(E18<>0,(SUM(B18

Oct-

=IF(K18<>0,K18,(IF(J18<>0,(Sum(B18:J18)/8),IF(I18<>0,(Sum(B18:I18)/8),I
F(H18<>0,(Sum(B18:H18)/7),IF(G18<>0,(Sum(B18:G18)/6),IF(F18<>0,(Sum(B18

 

[Bob Sullivan]

-----Original Message-----
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

May - =IF(F18<>0,F18,(IF(E18<>0,(SUM(B18:E18)/4),IF

Jun - =IF(G18<>0,G18,(IF(F18<>0,(SUM(B18:F18)/5),IF

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

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

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

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

Regards
Fred Newton
Zurich Financial Services
.

Dear Fred,

While this may not be what you want, the easiest solution
to your situation is to replace all those if statements
with a macro. If you need to test twelve different
conditions, then a Select Case statement in a macro will
do this for you. You can even create this macro as a user
function, and then use it as you would a function in a
cell.

Bob Sullivan

 

[Fred Newton]

Hi Frank

Thanks for the help, however that results in a value of 1 where the
cell above contains a zero. The SUMIF only sums the cells and the
result or the SUM is then divided by the result of the SUMIF.

=IF(B18<>0,B18,SUM($B$18:B$18)/SUMIF($B$18:B$18,"<>" & 0))

I replaced the SUMIF with a COUNTIF and it worked a treat.

Thanks again for the pointer, at least it got me out of the IF
mindset.

Regards
Fred

 

[Fred Newton]

Hi Bob

Thanks for the pointer, and, yes, I agree, however whilst I know and
use Excel, i'm a relative novice as far as macros are concerned.
Whilst I could probably make a fairly good attempt at putting it
together i'd have problems with "You can even create this macro as a
user function, and then use it as you would a function in a cell."
That went straight over my head i'm afraid.

Following on from Franks suggestion, I have a very simple resolution
to the problem and have implemented that.

Thanks again for your time

Regards
Fred