Re: sum, ave with limits, help pls.
- From: Domenic <someone@xxxxxxxxxx>
- Date: Mon, 03 Nov 2008 07:53:16 -0500
Try...
=LOOKUP(REPT("z",255),CHOOSE({1,2},"",SUM(IF(($C$1:$G$1=I2)*(COLUMN($C$1:
$G$1)<=SMALL(IF($C$1:$G$1=I2,COLUMN($C$1:$G$1)),MIN(2,COUNTIF($C$1:$G$1,I
2)))),$C$5:$G$5))/SUM(SUMIF($A$2:$A$4,IF(ISNUMBER($C$2:$G$4)*($C$1:$G$1=I
2)*(COLUMN($C$1:$G$1)<=SMALL(IF($C$1:$G$1=I2,COLUMN($C$1:$G$1)),MIN(2,COU
NTIF($C$1:$G$1,I2)))),$A$2:$A$4),$B$2:$B$4))&""))
....confirmed with CONTROL+SHIFT+ENTER. Note that while the formula
returns a number when data is present the number is formatted as text.
Here's an alternative which returns a numerical value...
=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,SUM(IF(($C$1:$G$1=I2)*(COLUM
N($C$1:$G$1)<=SMALL(IF($C$1:$G$1=I2,COLUMN($C$1:$G$1)),MIN(2,COUNTIF($C$1
:$G$1,I2)))),$C$5:$G$5))/SUM(SUMIF($A$2:$A$4,IF(ISNUMBER($C$2:$G$4)*($C$1
:$G$1=I2)*(COLUMN($C$1:$G$1)<=SMALL(IF($C$1:$G$1=I2,COLUMN($C$1:$G$1)),MI
N(2,COUNTIF($C$1:$G$1,I2)))),$A$2:$A$4),$B$2:$B$4))))
....confirmed with CONTROL+SHIFT+ENTER. Note, however, the formula
returns 0 even when data is not present.
Hope this helps!
In article <C527CF3E-44FD-4FC6-806F-D8A1CDC94505@xxxxxxxxxxxxx>,
Chris <Chris@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Hi,.
I have one more question, Like I said, I combined the formulas so that it
would average the data but if there is no data, it shows as #DIV/0!. I need
this to show as blank or empty
this is BTW the formula that I used. I tried adding another If condition
with "" as an option but it wont work, pls help. Tnx again.
=SUM(IF(DIO!$D$3:$FG$3=A10,IF(COLUMN(DIO!$D$3:$FG$3)<=SMALL(IF(DIO!$D$3:$FG$3=
A10,COLUMN(DIO!$D$3:$FG$3)),MIN(8,COUNTIF(DIO!$D$3:$FG$3,A10))),DIO!$D$8:$FG$8
)))/SUM(SUMIF(DIO!$B$4:$B$7,IF(ISNUMBER(DIO!$D$4:$FG$7),IF(DIO!$D$3:$FG$3=A10,
IF(COLUMN(DIO!$D$3:$FG$3)<=SMALL(IF(DIO!$D$3:$FG$3=A10,COLUMN(DIO!$D$3:$FG$3))
,MIN(8,COUNTIF(DIO!$D$3:$FG$3,A10))),DIO!$B$4:$B$7))),DIO!$C$4:$C$7))
- References:
- sum, ave with limits, help pls.
- From: Chris
- Re: sum, ave with limits, help pls.
- From: Domenic
- Re: sum, ave with limits, help pls.
- From: Chris
- Re: sum, ave with limits, help pls.
- From: Domenic
- Re: sum, ave with limits, help pls.
- From: Chris
- sum, ave with limits, help pls.
- Prev by Date: Problem printing lines
- Next by Date: RE: Converting an Array to value's
- Previous by thread: Re: sum, ave with limits, help pls.
- Next by thread: RE: sum, ave with limits, help pls.
- Index(es):
Relevant Pages
|
