Mulitple Workbooks and Ignoring Blank Cells
 

I am working with multpile workbooks. (Site 1, Site2..ect). I have a master
workbook that pulls data (Rating numbers from each Site workbook). The master
book compliles and averages all the data. The problem is that if a Site
workbook contains a blank cell it places a zero in the materbook. The zero is
counted and effects the overall average. The critera for rating is 0-10 so I
need to capture the zero but if the cell is blank I need the master cell to
remain Blank. Here is what I have so far.

Site 1 workbook In cell P-22 (=SUMIF(B22:F22,"<""")

Master book formula for retrieving the data In cell E-10 ([Site 1 Manager
Performance Assessment.xls]MPAS'!$P$22

Masterbook formula for rating the value
=IF(COUNTA(E10:E26)=0,"",AVERAGE(E10:E26))

Thanks in advance your time is always appreciated on this end.

Mulitple Workbooks and Ignoring Blank Cells
 

You actually have two problems:

1. you must carry the blank formward:

=IF([Site 1 Manager Performance Assessment.xls]MPAS'!$P$22="","",[Site 1
Manager Performance Assessment.xls]MPAS'!$P$22)

2. Don't use COUNTA:

=AVERAGE(E10:E26)

because COUNTA will count formulaic blanks just like non-blanks.
--
Gary''s Student - gsnu200902


"Larry" wrote:

I am working with multpile workbooks. (Site 1, Site2..ect). I have a master
workbook that pulls data (Rating numbers from each Site workbook). The master
book compliles and averages all the data. The problem is that if a Site
workbook contains a blank cell it places a zero in the materbook. The zero is
counted and effects the overall average. The critera for rating is 0-10 so I
need to capture the zero but if the cell is blank I need the master cell to
remain Blank. Here is what I have so far.

Site 1 workbook In cell P-22 (=SUMIF(B22:F22,"<""")

Master book formula for retrieving the data In cell E-10 ([Site 1 Manager
Performance Assessment.xls]MPAS'!$P$22

Masterbook formula for rating the value
=IF(COUNTA(E10:E26)=0,"",AVERAGE(E10:E26))

Thanks in advance your time is always appreciated on this end.

Mulitple Workbooks and Ignoring Blank Cells
 

This is the formula I used and I still get a return of 0.0 in the target cell

=IF('[ATHN Site Manager Performance Assessment.xls]MPAS'!$P$22="","",'[ATHN
Site Manager Performance Assessment.xls]MPAS'!$P$22)

"Gary''s Student" wrote:

You actually have two problems:

1. you must carry the blank formward:

=IF([Site 1 Manager Performance Assessment.xls]MPAS'!$P$22="","",[Site 1
Manager Performance Assessment.xls]MPAS'!$P$22)

2. Don't use COUNTA:

=AVERAGE(E10:E26)

because COUNTA will count formulaic blanks just like non-blanks.
--
Gary''s Student - gsnu200902


"Larry" wrote:

I am working with multpile workbooks. (Site 1, Site2..ect). I have a master
workbook that pulls data (Rating numbers from each Site workbook). The master
book compliles and averages all the data. The problem is that if a Site
workbook contains a blank cell it places a zero in the materbook. The zero is
counted and effects the overall average. The critera for rating is 0-10 so I
need to capture the zero but if the cell is blank I need the master cell to
remain Blank. Here is what I have so far.

Site 1 workbook In cell P-22 (=SUMIF(B22:F22,"<""")

Master book formula for retrieving the data In cell E-10 ([Site 1 Manager
Performance Assessment.xls]MPAS'!$P$22

Masterbook formula for rating the value
=IF(COUNTA(E10:E26)=0,"",AVERAGE(E10:E26))

Thanks in advance your time is always appreciated on this end.

Mulitple Workbooks and Ignoring Blank Cells
 

Were both workbooks open when you observe the zero ?
--
Gary''s Student - gsnu200902


"Larry" wrote:

This is the formula I used and I still get a return of 0.0 in the target cell

=IF('[ATHN Site Manager Performance Assessment.xls]MPAS'!$P$22="","",'[ATHN
Site Manager Performance Assessment.xls]MPAS'!$P$22)

"Gary''s Student" wrote:

You actually have two problems:

1. you must carry the blank formward:

=IF([Site 1 Manager Performance Assessment.xls]MPAS'!$P$22="","",[Site 1
Manager Performance Assessment.xls]MPAS'!$P$22)

2. Don't use COUNTA:

=AVERAGE(E10:E26)

because COUNTA will count formulaic blanks just like non-blanks.
--
Gary''s Student - gsnu200902


"Larry" wrote:

I am working with multpile workbooks. (Site 1, Site2..ect). I have a master
workbook that pulls data (Rating numbers from each Site workbook). The master
book compliles and averages all the data. The problem is that if a Site
workbook contains a blank cell it places a zero in the materbook. The zero is
counted and effects the overall average. The critera for rating is 0-10 so I
need to capture the zero but if the cell is blank I need the master cell to
remain Blank. Here is what I have so far.

Site 1 workbook In cell P-22 (=SUMIF(B22:F22,"<""")

Master book formula for retrieving the data In cell E-10 ([Site 1 Manager
Performance Assessment.xls]MPAS'!$P$22

Masterbook formula for rating the value
=IF(COUNTA(E10:E26)=0,"",AVERAGE(E10:E26))

Thanks in advance your time is always appreciated on this end.

Mulitple Workbooks and Ignoring Blank Cells
 

yes

"Gary''s Student" wrote:

Were both workbooks open when you observe the zero ?
--
Gary''s Student - gsnu200902


"Larry" wrote:

This is the formula I used and I still get a return of 0.0 in the target cell

=IF('[ATHN Site Manager Performance Assessment.xls]MPAS'!$P$22="","",'[ATHN
Site Manager Performance Assessment.xls]MPAS'!$P$22)

"Gary''s Student" wrote:

You actually have two problems:

1. you must carry the blank formward:

=IF([Site 1 Manager Performance Assessment.xls]MPAS'!$P$22="","",[Site 1
Manager Performance Assessment.xls]MPAS'!$P$22)

2. Don't use COUNTA:

=AVERAGE(E10:E26)

because COUNTA will count formulaic blanks just like non-blanks.
--
Gary''s Student - gsnu200902


"Larry" wrote:

I am working with multpile workbooks. (Site 1, Site2..ect). I have a master
workbook that pulls data (Rating numbers from each Site workbook). The master
book compliles and averages all the data. The problem is that if a Site
workbook contains a blank cell it places a zero in the materbook. The zero is
counted and effects the overall average. The critera for rating is 0-10 so I
need to capture the zero but if the cell is blank I need the master cell to
remain Blank. Here is what I have so far.

Site 1 workbook In cell P-22 (=SUMIF(B22:F22,"<""")

Master book formula for retrieving the data In cell E-10 ([Site 1 Manager
Performance Assessment.xls]MPAS'!$P$22

Masterbook formula for rating the value
=IF(COUNTA(E10:E26)=0,"",AVERAGE(E10:E26))

Thanks in advance your time is always appreciated on this end.

Mulitple Workbooks and Ignoring Blank Cells
 

I did try both ways one with both open and the managers report by it's self

"Gary''s Student" wrote:

Were both workbooks open when you observe the zero ?
--
Gary''s Student - gsnu200902


"Larry" wrote:

This is the formula I used and I still get a return of 0.0 in the target cell

=IF('[ATHN Site Manager Performance Assessment.xls]MPAS'!$P$22="","",'[ATHN
Site Manager Performance Assessment.xls]MPAS'!$P$22)

"Gary''s Student" wrote:

You actually have two problems:

1. you must carry the blank formward:

=IF([Site 1 Manager Performance Assessment.xls]MPAS'!$P$22="","",[Site 1
Manager Performance Assessment.xls]MPAS'!$P$22)

2. Don't use COUNTA:

=AVERAGE(E10:E26)

because COUNTA will count formulaic blanks just like non-blanks.
--
Gary''s Student - gsnu200902


"Larry" wrote:

I am working with multpile workbooks. (Site 1, Site2..ect). I have a master
workbook that pulls data (Rating numbers from each Site workbook). The master
book compliles and averages all the data. The problem is that if a Site
workbook contains a blank cell it places a zero in the materbook. The zero is
counted and effects the overall average. The critera for rating is 0-10 so I
need to capture the zero but if the cell is blank I need the master cell to
remain Blank. Here is what I have so far.

Site 1 workbook In cell P-22 (=SUMIF(B22:F22,"<""")

Master book formula for retrieving the data In cell E-10 ([Site 1 Manager
Performance Assessment.xls]MPAS'!$P$22

Masterbook formula for rating the value
=IF(COUNTA(E10:E26)=0,"",AVERAGE(E10:E26))

Thanks in advance your time is always appreciated on this end.