This is pretty basic but its the best that I could do not knowing VBA or an easier way. I hope someone can help in making it better. The formula works but under very strict conditions. The spreadsheet has columns for all 12 months, a yearly total and an average column. The If Then is for the average column for entire year. No matter what the month it will calculate the average using the appropriate number of months to divide by, now heres the catch; each previous month has to have a number greater than 0 and luckily I have but if a previous month has a "0" it blows the formula out of the water.
I had to split the into IF-Then into two sections because of the embedding limitations so when I have a lot of items that I need to do this for you can see I am having to reproduce this formula multiple times and tracking gets a little confusing.
Jan Column C Feb Column D Mar Column E Apr Column F May Column G
Jun Column H Jul Column I Aug Column J C Sept Column K Oct Column L
Nov Column M Dec Column N
Jan thru June: (Cell V4)
=IF((AND($C$19>0,$D$19<=0)),$C$19,IF((AND($D$19>0,$E$19<=0)),(($C$19+$D$19)/2),IF((AND($E$19>0,$F$19<=0)),(($C$19+$D$19+$E$19)/3),IF((AND($F$19>0,$G$19<=0)),(($C$19+$D$19+$E$19+$F$19)/4),IF((AND($G$19>0,$H$19<=0)),(($C$19+$D$19+$E$19+$F$19+$G$19)/5),IF((AND($H$19>0,$I$19<=0)),(($C$19+$D$19+$E$19+$F$19+$G$19+$H$19)/6),0))))))
July thru Dec: (Cell V5)
=IF((AND($I$19>0,$J$19<=0)),(($C$19+$D$19+$E$19+$F$19+$G$19+$H$19+$I$19)/7),IF((AND($J$19>0,$K$19<=0)),(($C$19+$D$19+$E$19+$F$19+$G$19+$H$19+$I$19+$J$19)/8),IF((AND($K$19>0,$L$19<=0)),(($C$19+$D$19+$E$19+$F$19+$G$19+$H$19+$I$19+$J$19+$K$19)/9),IF((AND($L$19>0,$M$19<=0)),(($C$19+$D$19+$E$19+$F$19+$G$19+$H$19+$I$19+$J$19+$K$19+$L$19)/10),IF((AND($M$19>0,$N$19<=0)),(($C$19+$D$19+$E$19+$F$19+$G$19+$H$19+$I$19+$J$19+$K$19+$L$19+$M$19)/11),IF(($N$19>0),(($C$19+$D$19+$E$19+$F$19+$G$19+$H$19+$I$19+$J$19+$K$19+$L$19+$M$19+$N$19)/12),0))))))
Average Column: (Cell I19 is the cell for July Average for this particular Item)
=IF(I19>0,V5,V4)
Bookmarks