Averaging Array Formula
 

Hi:

The inelegant formula below is an attempt to average values in G10, J10,
M10, and P10 if the cell to the right of each one is greater than 4. (That
is, if the cell to the right is 4 or less, the number to the left needs to be
tossed out of the calculation completely and only the remaining numbers
averaged):

=IF(ISERROR(AVERAGE(IF(H10<=4,0,G10),IF(K10<=4,0,J 10),IF(N10<=4,0,M10),IF(Q10<=4,0,P10))),"",AVERAGE (IF(H10<=4,0,G10),IF(K10<=4,0,J10),IF(N10<=4,0,M10 ),IF(Q10<=4,0,P10)))

As you can see, though, instead of tossing out the number to the left
completely, it just uses a zero instead, which completely screws the average
I need. I've tried substituting double quotes for the zeroes, but of couse
that doesn't work, either.

Any ideas on how this whole thing can be reconfigured as an array formula to
do what I need it to do? I keep getting lost in the syntax and getting
useless results.

Help!

Thanks in advance!

Michael Link

Averaging Array Formula
 

Hi!

Try this:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=AVERAGE(IF(MOD(COLUMN(G10:Q10),3)=1,IF(MOD(COLUMN (H10:Q10),3)=2,IF(H10:Q104,G10:Q10))))

NB: if all 4 cells to the right are <=4 then you'll get a #DIV/0! error. I
can put an error trap in the formula which will make it longer.

Biff

"Michael Link" wrote in message
...
Hi:

The inelegant formula below is an attempt to average values in G10, J10,
M10, and P10 if the cell to the right of each one is greater than 4. (That
is, if the cell to the right is 4 or less, the number to the left needs to
be
tossed out of the calculation completely and only the remaining numbers
averaged):

=IF(ISERROR(AVERAGE(IF(H10<=4,0,G10),IF(K10<=4,0,J 10),IF(N10<=4,0,M10),IF(Q10<=4,0,P10))),"",AVERAGE (IF(H10<=4,0,G10),IF(K10<=4,0,J10),IF(N10<=4,0,M10 ),IF(Q10<=4,0,P10)))

As you can see, though, instead of tossing out the number to the left
completely, it just uses a zero instead, which completely screws the
average
I need. I've tried substituting double quotes for the zeroes, but of couse
that doesn't work, either.

Any ideas on how this whole thing can be reconfigured as an array formula
to
do what I need it to do? I keep getting lost in the syntax and getting
useless results.

Help!

Thanks in advance!

Michael Link

Averaging Array Formula
 

Michael,

=SUM(IF(H10=4,G10,0),IF(K10=4,J10,0),IF(N10=4,M 10,0),IF(Q10=4,P10,0))/SUM(IF(H10=4,1,0),IF(K10=4,1,0),IF(N10=4,1,0),I F(N10=4,1,0))

HTH,
Bernie
MS Excel MVP


"Michael Link" wrote in message
...
Hi:

The inelegant formula below is an attempt to average values in G10, J10,
M10, and P10 if the cell to the right of each one is greater than 4. (That
is, if the cell to the right is 4 or less, the number to the left needs to be
tossed out of the calculation completely and only the remaining numbers
averaged):

=IF(ISERROR(AVERAGE(IF(H10<=4,0,G10),IF(K10<=4,0,J 10),IF(N10<=4,0,M10),IF(Q10<=4,0,P10))),"",AVERAGE (IF(H10<=4,0,G10),IF(K10<=4,0,J10),IF(N10<=4,0,M10 ),IF(Q10<=4,0,P10)))

As you can see, though, instead of tossing out the number to the left
completely, it just uses a zero instead, which completely screws the average
I need. I've tried substituting double quotes for the zeroes, but of couse
that doesn't work, either.

Any ideas on how this whole thing can be reconfigured as an array formula to
do what I need it to do? I keep getting lost in the syntax and getting
useless results.

Help!

Thanks in advance!

Michael Link

Averaging Array Formula
 

Or the array formula - entered using Ctrl-Shift-Enter

=AVERAGE(IF((MOD(COLUMN(G10:Q10),3)=2)*(G10:Q10)= 4,F10:P10,""))

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Michael,

=SUM(IF(H10=4,G10,0),IF(K10=4,J10,0),IF(N10=4,M 10,0),IF(Q10=4,P10,0))/SUM(IF(H10=4,1,0),IF(K10=4,1,0),IF(N10=4,1,0),I F(N10=4,1,0))

HTH,
Bernie
MS Excel MVP


"Michael Link" wrote in message
...
Hi:

The inelegant formula below is an attempt to average values in G10, J10,
M10, and P10 if the cell to the right of each one is greater than 4. (That
is, if the cell to the right is 4 or less, the number to the left needs to be
tossed out of the calculation completely and only the remaining numbers
averaged):

=IF(ISERROR(AVERAGE(IF(H10<=4,0,G10),IF(K10<=4,0,J 10),IF(N10<=4,0,M10),IF(Q10<=4,0,P10))),"",AVERAGE (IF(H10<=4,0,G10),IF(K10<=4,0,J10),IF(N10<=4,0,M10 ),IF(Q10<=4,0,P10)))

As you can see, though, instead of tossing out the number to the left
completely, it just uses a zero instead, which completely screws the average
I need. I've tried substituting double quotes for the zeroes, but of couse
that doesn't work, either.

Any ideas on how this whole thing can be reconfigured as an array formula to
do what I need it to do? I keep getting lost in the syntax and getting
useless results.

Help!

Thanks in advance!

Michael Link

Averaging Array Formula
 

Ooops!

I have the range references messed up!

Should be:

=AVERAGE(IF(MOD(COLUMN(G10:P10),3)=1,IF(MOD(COLUMN (H10:Q10),3)=2,IF(H10:Q104,G10:P10))))

Biff

"Biff" wrote in message
...
Hi!

Try this:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=AVERAGE(IF(MOD(COLUMN(G10:Q10),3)=1,IF(MOD(COLUMN (H10:Q10),3)=2,IF(H10:Q104,G10:Q10))))

NB: if all 4 cells to the right are <=4 then you'll get a #DIV/0! error. I
can put an error trap in the formula which will make it longer.

Biff

"Michael Link" wrote in message
...
Hi:

The inelegant formula below is an attempt to average values in G10, J10,
M10, and P10 if the cell to the right of each one is greater than 4.
(That
is, if the cell to the right is 4 or less, the number to the left needs
to be
tossed out of the calculation completely and only the remaining numbers
averaged):

=IF(ISERROR(AVERAGE(IF(H10<=4,0,G10),IF(K10<=4,0,J 10),IF(N10<=4,0,M10),IF(Q10<=4,0,P10))),"",AVERAGE (IF(H10<=4,0,G10),IF(K10<=4,0,J10),IF(N10<=4,0,M10 ),IF(Q10<=4,0,P10)))

As you can see, though, instead of tossing out the number to the left
completely, it just uses a zero instead, which completely screws the
average
I need. I've tried substituting double quotes for the zeroes, but of
couse
that doesn't work, either.

Any ideas on how this whole thing can be reconfigured as an array formula
to
do what I need it to do? I keep getting lost in the syntax and getting
useless results.

Help!

Thanks in advance!

Michael Link

Averaging Array Formula
 

Hi,

Try the following formula [which is also not very elegant(!)]

=SUM(IF(H104,G10,0)+IF(K104,J10,0)+IF(N104,M10, 0)+IF(Q104,P10,0))/SUM(IF(H104,1,0)+IF(K104,1,0)+IF(N104,1,0)+IF(Q 104,1,0))

If none of the values in H10, K10, N10, Q10 is greater than 4, the formula
will return
#DIV/0! error. If you want to avoid that error showing up, try the
following modification.

=IF(SUM(IF(H104,1,0)+IF(K104,1,0)+IF(N104,1,0)+ IF(Q104,1,0))0,SUM(IF(H104,G10,0)+IF(K104,J10, 0)+IF(N104,M10,0)+IF(Q104,P10,0))/SUM(IF(H104,1,0)+IF(K104,1,0)+IF(N104,1,0)+IF(Q 104,1,0)),"")

Regards,
B. R. Ramachandran

"Michael Link" wrote:

Hi:

The inelegant formula below is an attempt to average values in G10, J10,
M10, and P10 if the cell to the right of each one is greater than 4. (That
is, if the cell to the right is 4 or less, the number to the left needs to be
tossed out of the calculation completely and only the remaining numbers
averaged):

=IF(ISERROR(AVERAGE(IF(H10<=4,0,G10),IF(K10<=4,0,J 10),IF(N10<=4,0,M10),IF(Q10<=4,0,P10))),"",AVERAGE (IF(H10<=4,0,G10),IF(K10<=4,0,J10),IF(N10<=4,0,M10 ),IF(Q10<=4,0,P10)))

As you can see, though, instead of tossing out the number to the left
completely, it just uses a zero instead, which completely screws the average
I need. I've tried substituting double quotes for the zeroes, but of couse
that doesn't work, either.

Any ideas on how this whole thing can be reconfigured as an array formula to
do what I need it to do? I keep getting lost in the syntax and getting
useless results.

Help!

Thanks in advance!

Michael Link