Mulitple Workbooks and Ignoring Blank Cells

L

Larry

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.
 
G

Gary''s Student

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.
 
L

Larry

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 said:
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 said:
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.
Click to expand...
Click to expand...
 
G

Gary''s Student

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


Larry said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
L

Larry

yes

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


Larry said:
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 said:
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


:

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.
Click to expand...
Click to expand...
Click to expand...
 
L

Larry

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

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


Larry said:
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 said:
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


:

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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Ignoring Blank cells 9
Linking Master Workbook & subordinate workbook 1
Macro to open, Update links, save and close workbooks 0
Help! Need to ignore 0s and blank cells in average formula! 2
Help making this macro dynamic :) 1
cells with zero value are shown as blank, not zero 2
Link Workbooks 1
merge 3 workbooks (and all coniciding worksheets) into 1 master wb 0

Top