Receiving #VALUE! error for my average formula
 

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(ISN UMBER(B13:B15),(B13:B15),FALSE),(IF(ISNUMBER(B19:B 21),(B19:B21),FALSE),(IF(ISNUMBER(B25:B27),(B25:B2 7),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!

Receiving #VALUE! error for my average formula
 

This would probably work


=SUM(SUMIF(B7:B9,"<"&99^99),SUMIF(B13:B15,"<"&99^9 9),SUMIF(B19:B21,"<"&99^99),SUMIF(B25:B27,"<"&99^9 9))/SUM(COUNTIF(B7:B9,"<"&99^99),COUNTIF(B13:B15,"<"&9 9^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" wrote in message
...
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(ISN UMBER(B13:B15),(B13:B15),FALSE),(IF(ISNUMBER(B19:B 21),(B19:B21),FALSE),(IF(ISNUMBER(B25:B27),(B25:B2 7),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!

Receiving #VALUE! error for my average formula
 

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

"Peo Sjoblom" wrote:

This would probably work


=SUM(SUMIF(B7:B9,"<"&99^99),SUMIF(B13:B15,"<"&99^9 9),SUMIF(B19:B21,"<"&99^99),SUMIF(B25:B27,"<"&99^9 9))/SUM(COUNTIF(B7:B9,"<"&99^99),COUNTIF(B13:B15,"<"&9 9^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" wrote in message
...
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(ISN UMBER(B13:B15),(B13:B15),FALSE),(IF(ISNUMBER(B19:B 21),(B19:B21),FALSE),(IF(ISNUMBER(B25:B27),(B25:B2 7),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!