I am creating a spread sheet that has calculations from the raw data that is
entered monthly. The calculation data and some data are then refered to in
seperate tabs that create a dashboard display with appropriate graphs in a
tab. My question is, how do I set up the calculations to only recognize for
the months up to the report date? I have some of the calculations set up to
see 0 as 100% when there are no entries and then when you go to the quarters
and YTD, they all average the dates that do not have data since we have not
gotten to those months yet. I would like to get this set up so that the raw
data can be entered each month from the diffenet groups and I do not have to
reset the calculations to include each new month. It does nothing to show a
YTD total that has 10 months of 100% averaged in.

Always structure you formulae to return a blank rather than zero for missing
data or data yet to be entered.


The AVERAGE() function ignores blanks, but treats 0 as a real value to be
averaged.
--
Gary's Student
gsnu200708


"Balzyone" wrote:

I am creating a spread sheet that has calculations from the raw data that is
entered monthly. The calculation data and some data are then refered to in
seperate tabs that create a dashboard display with appropriate graphs in a
tab. My question is, how do I set up the calculations to only recognize for
the months up to the report date? I have some of the calculations set up to
see 0 as 100% when there are no entries and then when you go to the quarters
and YTD, they all average the dates that do not have data since we have not
gotten to those months yet. I would like to get this set up so that the raw
data can be entered each month from the diffenet groups and I do not have to
reset the calculations to include each new month. It does nothing to show a
YTD total that has 10 months of 100% averaged in.

How would I structure the calculation to recognoze that we are at 100% if we
did not have any of that function for the month and not have anything average
for months that have no data?

"Gary''s Student" wrote:

Always structure you formulae to return a blank rather than zero for missing
data or data yet to be entered.


The AVERAGE() function ignores blanks, but treats 0 as a real value to be
averaged.
--
Gary's Student
gsnu200708


"Balzyone" wrote:

I am creating a spread sheet that has calculations from the raw data that is
entered monthly. The calculation data and some data are then refered to in
seperate tabs that create a dashboard display with appropriate graphs in a
tab. My question is, how do I set up the calculations to only recognize for
the months up to the report date? I have some of the calculations set up to
see 0 as 100% when there are no entries and then when you go to the quarters
and YTD, they all average the dates that do not have data since we have not
gotten to those months yet. I would like to get this set up so that the raw
data can be entered each month from the diffenet groups and I do not have to
reset the calculations to include each new month. It does nothing to show a
YTD total that has 10 months of 100% averaged in.

Here is a typical example that you might be able to adapt:

In column A we have dates
in column B we sales targets for the year (all filled in)
In column C we enter the monthly sales (partially filled in)
In column D we have the percents as this formula:
=C1/B1
Notice that the percents are zero if there is no data in column C

the formula
=AVERAGE(D1:D100) includes these phony zeros


If we change the column D formulae to:
=IF(C1="","",C1/B1)
then
=AVERAGE(D1:D100) will only average the genuine (non-blank) data.

--
Gary''s Student
gsnu200708


"Balzyone" wrote:

How would I structure the calculation to recognoze that we are at 100% if we
did not have any of that function for the month and not have anything average
for months that have no data?

"Gary''s Student" wrote:

Always structure you formulae to return a blank rather than zero for missing
data or data yet to be entered.


The AVERAGE() function ignores blanks, but treats 0 as a real value to be
averaged.
--
Gary's Student
gsnu200708


"Balzyone" wrote:

I am creating a spread sheet that has calculations from the raw data that is
entered monthly. The calculation data and some data are then refered to in
seperate tabs that create a dashboard display with appropriate graphs in a
tab. My question is, how do I set up the calculations to only recognize for
the months up to the report date? I have some of the calculations set up to
see 0 as 100% when there are no entries and then when you go to the quarters
and YTD, they all average the dates that do not have data since we have not
gotten to those months yet. I would like to get this set up so that the raw
data can be entered each month from the diffenet groups and I do not have to
reset the calculations to include each new month. It does nothing to show a
YTD total that has 10 months of 100% averaged in.

Well I dug into this a lot deeper and found something that worked. I am
going to share this so if anyone else is doing the same...... I used the
ISBLANK(value) function. this allowed me have my normal calculation as the
false, so if there was an empty cell, it would leave the cell blank. here is
my calculation
{=IF(ISBLANK(D2),"",(IF((D2+D3+D4=0),1,((D2+D3+D4)-D5)/(D2+D3+D4))))}. It is
a bit long but it allowed me to average only for the months that I have
entered data. In the columns that I had each quarter broken out to I had
this calc {=IF(ISBLANK(B6),"",(AVERAGE(B6:D6)))}. Then for the YTD it was a
simple average of the 4 quarters.

"Gary''s Student" wrote:

Here is a typical example that you might be able to adapt:

In column A we have dates
in column B we sales targets for the year (all filled in)
In column C we enter the monthly sales (partially filled in)
In column D we have the percents as this formula:
=C1/B1
Notice that the percents are zero if there is no data in column C

the formula
=AVERAGE(D1:D100) includes these phony zeros


If we change the column D formulae to:
=IF(C1="","",C1/B1)
then
=AVERAGE(D1:D100) will only average the genuine (non-blank) data.

--
Gary''s Student
gsnu200708


"Balzyone" wrote:

How would I structure the calculation to recognoze that we are at 100% if we
did not have any of that function for the month and not have anything average
for months that have no data?

"Gary''s Student" wrote:

Always structure you formulae to return a blank rather than zero for missing
data or data yet to be entered.


The AVERAGE() function ignores blanks, but treats 0 as a real value to be
averaged.
--
Gary's Student
gsnu200708


"Balzyone" wrote:

I am creating a spread sheet that has calculations from the raw data that is
entered monthly. The calculation data and some data are then refered to in
seperate tabs that create a dashboard display with appropriate graphs in a
tab. My question is, how do I set up the calculations to only recognize for
the months up to the report date? I have some of the calculations set up to
see 0 as 100% when there are no entries and then when you go to the quarters
and YTD, they all average the dates that do not have data since we have not
gotten to those months yet. I would like to get this set up so that the raw
data can be entered each month from the diffenet groups and I do not have to
reset the calculations to include each new month. It does nothing to show a
YTD total that has 10 months of 100% averaged in.