Receiving #VALUE! error for my average formula

[JAbels001]

I have created a workbook that calculates an emplopyee's perfomance average
for a 12month period. I have Jan-Dec, as well as, Quarter 1, Mid Year,
Quarter 3 and a year to date average. At this point we are at fiscal month
Oct so I do not have data for Oct to Dec yet, however I want the year to date
average to give me everything else so far. Example:
Jan - 13
Feb - 12.55
Mar - 12.83
Q1 - 12.79 (average of Jan - Mar)
Apr - 15.44
May - 15.44
Jun - 17.58
Mid year - 14.48 (average of Jan - Jun, excluding Q1 number)
July - 15.9
Aug - 15.9
Sept - 15.9
Q3 - 15.9 (average of July - Sept)
Oct -
Nov -
Dec -
YTD - #VALUE! (currently I am attempting to use formula
=AVERAGE(IF(ISNUMBER(B7:B9),(B7:B9),FALSE),(IF(ISNUMBER(B13:B15),(B13:B15),FALSE),(IF(ISNUMBER(B19:B21),(B19:B21),FALSE),(IF(ISNUMBER(B25:B27),(B25:B27),FALSE)))))

the main problem is I need the formula to negate blanks and #N/A b/c
sometimes the employee was not here for that month. Can anybody please help?
I have been racking my brain for days! Thanks!

 

[Peo Sjoblom]

This would probably work


=SUM(SUMIF(B7:B9,"<"&99^99),SUMIF(B13:B15,"<"&99^99),SUMIF(B19:B21,"<"&99^99),SUMIF(B25:B27,"<"&99^99))/SUM(COUNTIF(B7:B9,"<"&99^99),COUNTIF(B13:B15,"<"&99^99),COUNTIF(B19:B21,"<"&99^99),COUNTIF(B25:B27,"<"&99^99))

will ignore the errors and blanks


You might want to change the layout a bit so you have all the months
adjacent with no Quarters/Half years text etc
in-between.

Maybe you can group those on the side that way you could use something like
this


=AVERAGE(IF(ISNUMBER(B7:B18),B7:B18))



--


Regards,


Peo Sjoblom

 

[JAbels001]

That actually worked! You're a genius! Thanks!

This would probably work


=SUM(SUMIF(B7:B9,"<"&99^99),SUMIF(B13:B15,"<"&99^99),SUMIF(B19:B21,"<"&99^99),SUMIF(B25:B27,"<"&99^99))/SUM(COUNTIF(B7:B9,"<"&99^99),COUNTIF(B13:B15,"<"&99^99),COUNTIF(B19:B21,"<"&99^99),COUNTIF(B25:B27,"<"&99^99))

will ignore the errors and blanks


You might want to change the layout a bit so you have all the months
adjacent with no Quarters/Half years text etc
in-between.

Maybe you can group those on the side that way you could use something like
this


=AVERAGE(IF(ISNUMBER(B7:B18),B7:B18))



--


Regards,


Peo Sjoblom