IF Then - using 12 months - ongoing average

**JBeaucaire** · 03-07-2009, 08:38 PM

Could you just post up your workbook so we don 't have to waste time reconstructing it to test...?

GO ADVANCED and click on the paperclip icon.

**DonkeyOte** · 03-08-2009, 03:56 AM

Given the 0 issue the area of interest thus becomes the rows above and how they are populated... for ex... I presume row 19 is the sum of rows 2:18, are these rows populated with 0's for future months ? If yes, would you EVER have 0's for all rows as valid results ?

I would be inclined to alter the SUM in 19 such that a number is only displayed if there are actuals in preceding rows:

B19: =IF(AND(COUNT(B2:B18),COUNTIF(B2:B18,"<>0")),SUM(B2:B18),"")
copied to M19

You can then just use a standard AVERAGE

P19: AVERAGE(B19:M19)

FYI:

If 0 were not a potentially valid historical result the traditional approach would be one of:

=AVERAGE(IF(B19:M19,B19:M19))
entered with CTRL + SHIFT + ENTER

or

=AVERAGE(B19:INDEX(B19:M19,LOOKUP(2,1/(B19:M19<>0),COLUMN(B19:M19)-1)))

(either way I make the Avg in your sample 4717.72)

**harleypop** · 03-08-2009, 01:22 PM

[QUOTE=

=AVERAGE(B19:INDEX(B19:M19,LOOKUP(2,1/(B19:M19<>0),COLUMN(B19:M19)-1)))

[/QUOTE]

This works great for what I was doing, yes I noticed later that my cut and paste into an example spreadsheet gave the wrong result, my actual spreadsheet was to big to post.

Thank you DonkeyOte.