software

Ok, I don't know what to do to get the cells with I want to average that are
currently blank to show a blank cell if there is no number or a zero in the
cells, I think this just sounds confusing. Anywho, this is the formula I have
and it's showing a blank cell but that's all it does, or it errors and I
can't make it an array.=IF(SUMPRODUCT(--(MOD(ROW(D53:CY53),9)=0),--(D53:CY53gt;0))=0,quot;quot;,AVERAGE(IF((MOD(ROW(D53:CY53),9 )=0)*(D53:CY53lt;gt;0),D53:CY53)))

Anyone know what the problem is?

Thanks for the help.

This part is your problem

MOD(ROW(D53:CY53),9)

as there is only one row, row 53, and it alwqays evalutes to 0. You probably
want

=IF(SUMPRODUCT(--(MOD(COLUMN(D53:CY53),9)=0),--(D53:CY53gt;0))=0,quot;quot;,AVERAGE(IF
((MOD(COLUMN(D53:CY53),9)=0)*(D53:CY53lt;gt;0),D53:CY5 3)))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

quot;tearingoutmyhairquot; gt; wrote in
message ...
gt; Ok, I don't know what to do to get the cells with I want to average that
are
gt; currently blank to show a blank cell if there is no number or a zero in
the
gt; cells, I think this just sounds confusing. Anywho, this is the formula I
have
gt; and it's showing a blank cell but that's all it does, or it errors and I
gt; can't make it an array.
gt;
gt;
gt;
=IF(SUMPRODUCT(--(MOD(ROW(D53:CY53),9)=0),--(D53:CY53gt;0))=0,quot;quot;,AVERAGE(IF((M
OD(ROW(D53:CY53),9)=0)*(D53:CY53lt;gt;0),D53:CY53)))
gt;
gt; Anyone know what the problem is?
gt;
gt; Thanks for the help.
It gave me an error, and wouldn't let me enter it.

Well all the totals that's I'm trying to get the average of are in row 53,
so I figured I wanted ROW. I got that whole thing off of some other comment
in this place though, figured I'd give it a shot.

quot;Bob Phillipsquot; wrote:

gt; This part is your problem
gt;
gt; MOD(ROW(D53:CY53),9)
gt;
gt; as there is only one row, row 53, and it alwqays evalutes to 0. You probably
gt; want
gt;
gt; =IF(SUMPRODUCT(--(MOD(COLUMN(D53:CY53),9)=0),--(D53:CY53gt;0))=0,quot;quot;,AVERAGE(IF
gt; ((MOD(COLUMN(D53:CY53),9)=0)*(D53:CY53lt;gt;0),D53:CY5 3)))
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove xxx from email address if mailing direct)
gt;
gt; quot;tearingoutmyhairquot; gt; wrote in
gt; message ...
gt; gt; Ok, I don't know what to do to get the cells with I want to average that
gt; are
gt; gt; currently blank to show a blank cell if there is no number or a zero in
gt; the
gt; gt; cells, I think this just sounds confusing. Anywho, this is the formula I
gt; have
gt; gt; and it's showing a blank cell but that's all it does, or it errors and I
gt; gt; can't make it an array.
gt; gt;
gt; gt;
gt; gt;
gt; =IF(SUMPRODUCT(--(MOD(ROW(D53:CY53),9)=0),--(D53:CY53gt;0))=0,quot;quot;,AVERAGE(IF((M
gt; OD(ROW(D53:CY53),9)=0)*(D53:CY53lt;gt;0),D53:CY53)))
gt; gt;
gt; gt; Anyone know what the problem is?
gt; gt;
gt; gt; Thanks for the help.
gt;
gt;
gt;

Why are you doing the MOD then? That will exclude certain items that don't
match the MOD criteria, which in the formula that you presented was all of
them.

Why is =AVERAGE(D53:CY53) not sufficient?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

quot;tearingoutmyhairquot; gt; wrote in
message ...
gt; It gave me an error, and wouldn't let me enter it.
gt;
gt; Well all the totals that's I'm trying to get the average of are in row 53,
gt; so I figured I wanted ROW. I got that whole thing off of some other
comment
gt; in this place though, figured I'd give it a shot.
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; This part is your problem
gt; gt;
gt; gt; MOD(ROW(D53:CY53),9)
gt; gt;
gt; gt; as there is only one row, row 53, and it alwqays evalutes to 0. You
probably
gt; gt; want
gt; gt;
gt; gt;
=IF(SUMPRODUCT(--(MOD(COLUMN(D53:CY53),9)=0),--(D53:CY53gt;0))=0,quot;quot;,AVERAGE(IF
gt; gt; ((MOD(COLUMN(D53:CY53),9)=0)*(D53:CY53lt;gt;0),D53:CY5 3)))
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove xxx from email address if mailing direct)
gt; gt;
gt; gt; quot;tearingoutmyhairquot; gt; wrote in
gt; gt; message ...
gt; gt; gt; Ok, I don't know what to do to get the cells with I want to average
that
gt; gt; are
gt; gt; gt; currently blank to show a blank cell if there is no number or a zero
in
gt; gt; the
gt; gt; gt; cells, I think this just sounds confusing. Anywho, this is the formula
I
gt; gt; have
gt; gt; gt; and it's showing a blank cell but that's all it does, or it errors and
I
gt; gt; gt; can't make it an array.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt;
=IF(SUMPRODUCT(--(MOD(ROW(D53:CY53),9)=0),--(D53:CY53gt;0))=0,quot;quot;,AVERAGE(IF((M
gt; gt; OD(ROW(D53:CY53),9)=0)*(D53:CY53lt;gt;0),D53:CY53)))
gt; gt; gt;
gt; gt; gt; Anyone know what the problem is?
gt; gt; gt;
gt; gt; gt; Thanks for the help.
gt; gt;
gt; gt;
gt; gt;
These are the nonadjecent cells that I need to get the total average of.

D53,M53,V53,AE53,AN53,AW53,BF53,BO53,BX53,CG53,CP5 3,CY53

The =AVERAGE wont work because it's for future years and those months have
no numbers entered yet so have blank cells that I'm trying to average. I get
the #DIV/0! when I insert that formula because of the empty cells.
quot;Bob Phillipsquot; wrote:

gt; Why are you doing the MOD then? That will exclude certain items that don't
gt; match the MOD criteria, which in the formula that you presented was all of
gt; them.
gt;
gt; Why is =AVERAGE(D53:CY53) not sufficient?
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove xxx from email address if mailing direct)
gt;
gt; quot;tearingoutmyhairquot; gt; wrote in
gt; message ...
gt; gt; It gave me an error, and wouldn't let me enter it.
gt; gt;
gt; gt; Well all the totals that's I'm trying to get the average of are in row 53,
gt; gt; so I figured I wanted ROW. I got that whole thing off of some other
gt; comment
gt; gt; in this place though, figured I'd give it a shot.
gt; gt;
gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt;
gt; gt; gt; This part is your problem
gt; gt; gt;
gt; gt; gt; MOD(ROW(D53:CY53),9)
gt; gt; gt;
gt; gt; gt; as there is only one row, row 53, and it alwqays evalutes to 0. You
gt; probably
gt; gt; gt; want
gt; gt; gt;
gt; gt; gt;
gt; =IF(SUMPRODUCT(--(MOD(COLUMN(D53:CY53),9)=0),--(D53:CY53gt;0))=0,quot;quot;,AVERAGE(IF
gt; gt; gt; ((MOD(COLUMN(D53:CY53),9)=0)*(D53:CY53lt;gt;0),D53:CY5 3)))
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove xxx from email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;tearingoutmyhairquot; gt; wrote in
gt; gt; gt; message ...
gt; gt; gt; gt; Ok, I don't know what to do to get the cells with I want to average
gt; that
gt; gt; gt; are
gt; gt; gt; gt; currently blank to show a blank cell if there is no number or a zero
gt; in
gt; gt; gt; the
gt; gt; gt; gt; cells, I think this just sounds confusing. Anywho, this is the formula
gt; I
gt; gt; gt; have
gt; gt; gt; gt; and it's showing a blank cell but that's all it does, or it errors and
gt; I
gt; gt; gt; gt; can't make it an array.
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt;
gt; =IF(SUMPRODUCT(--(MOD(ROW(D53:CY53),9)=0),--(D53:CY53gt;0))=0,quot;quot;,AVERAGE(IF((M
gt; gt; gt; OD(ROW(D53:CY53),9)=0)*(D53:CY53lt;gt;0),D53:CY53)))
gt; gt; gt; gt;
gt; gt; gt; gt; Anyone know what the problem is?
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks for the help.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;

What you then is

=IF(SUMPRODUCT(--(MOD(COLUMN(D53:CY53),9)=4),--(D53:CY53gt;0))=0,quot;quot;,
AVERAGE(IF((MOD(COLUMN(D53:CY53),9)=4)*(D53:CY53lt;gt; 0),D53:CY53)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

quot;tearingoutmyhairquot; gt; wrote in
message ...
gt; These are the nonadjecent cells that I need to get the total average of.
gt;
gt; D53,M53,V53,AE53,AN53,AW53,BF53,BO53,BX53,CG53,CP5 3,CY53
gt;
gt; The =AVERAGE wont work because it's for future years and those months have
gt; no numbers entered yet so have blank cells that I'm trying to average. I
get
gt; the #DIV/0! when I insert that formula because of the empty cells.
gt;
gt;
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Why are you doing the MOD then? That will exclude certain items that
don't
gt; gt; match the MOD criteria, which in the formula that you presented was all
of
gt; gt; them.
gt; gt;
gt; gt; Why is =AVERAGE(D53:CY53) not sufficient?
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove xxx from email address if mailing direct)
gt; gt;
gt; gt; quot;tearingoutmyhairquot; gt; wrote in
gt; gt; message ...
gt; gt; gt; It gave me an error, and wouldn't let me enter it.
gt; gt; gt;
gt; gt; gt; Well all the totals that's I'm trying to get the average of are in row
53,
gt; gt; gt; so I figured I wanted ROW. I got that whole thing off of some other
gt; gt; comment
gt; gt; gt; in this place though, figured I'd give it a shot.
gt; gt; gt;
gt; gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; This part is your problem
gt; gt; gt; gt;
gt; gt; gt; gt; MOD(ROW(D53:CY53),9)
gt; gt; gt; gt;
gt; gt; gt; gt; as there is only one row, row 53, and it alwqays evalutes to 0. You
gt; gt; probably
gt; gt; gt; gt; want
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
=IF(SUMPRODUCT(--(MOD(COLUMN(D53:CY53),9)=0),--(D53:CY53gt;0))=0,quot;quot;,AVERAGE(IF
gt; gt; gt; gt; ((MOD(COLUMN(D53:CY53),9)=0)*(D53:CY53lt;gt;0),D53:CY5 3)))
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; HTH
gt; gt; gt; gt;
gt; gt; gt; gt; Bob Phillips
gt; gt; gt; gt;
gt; gt; gt; gt; (remove xxx from email address if mailing direct)
gt; gt; gt; gt;
gt; gt; gt; gt; quot;tearingoutmyhairquot; gt;
wrote in
gt; gt; gt; gt; message ...
gt; gt; gt; gt; gt; Ok, I don't know what to do to get the cells with I want to
average
gt; gt; that
gt; gt; gt; gt; are
gt; gt; gt; gt; gt; currently blank to show a blank cell if there is no number or a
zero
gt; gt; in
gt; gt; gt; gt; the
gt; gt; gt; gt; gt; cells, I think this just sounds confusing. Anywho, this is the
formula
gt; gt; I
gt; gt; gt; gt; have
gt; gt; gt; gt; gt; and it's showing a blank cell but that's all it does, or it errors
and
gt; gt; I
gt; gt; gt; gt; gt; can't make it an array.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
=IF(SUMPRODUCT(--(MOD(ROW(D53:CY53),9)=0),--(D53:CY53gt;0))=0,quot;quot;,AVERAGE(IF((M
gt; gt; gt; gt; OD(ROW(D53:CY53),9)=0)*(D53:CY53lt;gt;0),D53:CY53)))
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Anyone know what the problem is?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks for the help.
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;
More generically, you could use

=IF(SUMPRODUCT(--(MOD(COLUMN(D53:CY53),9)=MIN(COLUMN(D53:CY53))),--(D53:CY53
gt;0))=0,quot;quot;,
AVERAGE(IF((MOD(COLUMN(D53:CY53),9)=MIN(COLUMN(D53 :CY53)))*(D53:CY53lt;gt;0),D53
:CY53)))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

quot;tearingoutmyhairquot; gt; wrote in
message ...
gt; These are the nonadjecent cells that I need to get the total average of.
gt;
gt; D53,M53,V53,AE53,AN53,AW53,BF53,BO53,BX53,CG53,CP5 3,CY53
gt;
gt; The =AVERAGE wont work because it's for future years and those months have
gt; no numbers entered yet so have blank cells that I'm trying to average. I
get
gt; the #DIV/0! when I insert that formula because of the empty cells.
gt;
gt;
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Why are you doing the MOD then? That will exclude certain items that
don't
gt; gt; match the MOD criteria, which in the formula that you presented was all
of
gt; gt; them.
gt; gt;
gt; gt; Why is =AVERAGE(D53:CY53) not sufficient?
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove xxx from email address if mailing direct)
gt; gt;
gt; gt; quot;tearingoutmyhairquot; gt; wrote in
gt; gt; message ...
gt; gt; gt; It gave me an error, and wouldn't let me enter it.
gt; gt; gt;
gt; gt; gt; Well all the totals that's I'm trying to get the average of are in row
53,
gt; gt; gt; so I figured I wanted ROW. I got that whole thing off of some other
gt; gt; comment
gt; gt; gt; in this place though, figured I'd give it a shot.
gt; gt; gt;
gt; gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; This part is your problem
gt; gt; gt; gt;
gt; gt; gt; gt; MOD(ROW(D53:CY53),9)
gt; gt; gt; gt;
gt; gt; gt; gt; as there is only one row, row 53, and it alwqays evalutes to 0. You
gt; gt; probably
gt; gt; gt; gt; want
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
=IF(SUMPRODUCT(--(MOD(COLUMN(D53:CY53),9)=0),--(D53:CY53gt;0))=0,quot;quot;,AVERAGE(IF
gt; gt; gt; gt; ((MOD(COLUMN(D53:CY53),9)=0)*(D53:CY53lt;gt;0),D53:CY5 3)))
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; HTH
gt; gt; gt; gt;
gt; gt; gt; gt; Bob Phillips
gt; gt; gt; gt;
gt; gt; gt; gt; (remove xxx from email address if mailing direct)
gt; gt; gt; gt;
gt; gt; gt; gt; quot;tearingoutmyhairquot; gt;
wrote in
gt; gt; gt; gt; message ...
gt; gt; gt; gt; gt; Ok, I don't know what to do to get the cells with I want to
average
gt; gt; that
gt; gt; gt; gt; are
gt; gt; gt; gt; gt; currently blank to show a blank cell if there is no number or a
zero
gt; gt; in
gt; gt; gt; gt; the
gt; gt; gt; gt; gt; cells, I think this just sounds confusing. Anywho, this is the
formula
gt; gt; I
gt; gt; gt; gt; have
gt; gt; gt; gt; gt; and it's showing a blank cell but that's all it does, or it errors
and
gt; gt; I
gt; gt; gt; gt; gt; can't make it an array.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
=IF(SUMPRODUCT(--(MOD(ROW(D53:CY53),9)=0),--(D53:CY53gt;0))=0,quot;quot;,AVERAGE(IF((M
gt; gt; gt; gt; OD(ROW(D53:CY53),9)=0)*(D53:CY53lt;gt;0),D53:CY53)))
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Anyone know what the problem is?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks for the help.
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;