Averaging based on criteria

[Guest]

Hi al

I'm trying to find out an average rate across non sequential cells, if the value in a corresponding cell is not "#Value" or any other error or number less than 1

The correseponding cells are H13, H21, H29, H37, H4

The rate cells (to average) are C13, C21, C29, C37, C45 (are always on the same row as above but different column

I tried this formula but it's got a problem with the individual cells being nominated with comma's, however I need the formula to evaluate each cell separately before it includes it's data

=SUMIF(H13,H21,H29,H37,H45,">0",C13,C21,C29,C37,C45)/COUNTA(H13,H21,H37,H45)

Any help would be greatly appreciated.

 

[Frank Kabel]

Hi
not tested but try to define two names for both ranges (goto 'Insert -
Name - Define)

--
Regards
Frank Kabel
Frankfurt, Germany

Hi all

I'm trying to find out an average rate across non sequential cells,

if the value in a corresponding cell is not "#Value" or any other error
or number less than 1.

The correseponding cells are H13, H21, H29, H37, H45

The rate cells (to average) are C13, C21, C29, C37, C45 (are always

on the same row as above but different column)

I tried this formula but it's got a problem with the individual cells

being nominated with comma's, however I need the formula to evaluate
each cell separately before it includes it's data:

 

[Aladin Akyurek]

=AVERAGE(IF((MOD(ROW(H13:H45)-CELL("Row",$H$13)+0,8)=0)*ISNUMBER(H13:H45),IF
(H13:H45>=1,C13:C45)))

which must be confirmed with control+shift+enter instead of just with enter.

Hi all

I'm trying to find out an average rate across non sequential cells, if the

value in a corresponding cell is not "#Value" or any other error or number
less than 1.

The correseponding cells are H13, H21, H29, H37, H45

The rate cells (to average) are C13, C21, C29, C37, C45 (are always on the

same row as above but different column)

I tried this formula but it's got a problem with the individual cells

being nominated with comma's, however I need the formula to evaluate each
cell separately before it includes it's data:

 

[Guest]

Thanks Frank - but I tried defining the area and doing the formula as
=SUMIF(DP1_ST1_CPT,">0",Station1_DP1)/counta(DP1_ST1_CPT) but I got an answer of #VALUE! - I made sure the defined ranges were the same size too - any other suggestions or did I do this wrong

Thanks Aladin - but I need non-sequential cell addition otherwise the formula will calculate other active cells into the formula - can I still use your formula if I am not selecting a range of cells?

Also, if it helps - I do have a constant label for these rows appearing in column A which we could lookup (ie whether the H cells have #Value! or a number the corresponding A cells always have "Station 1" written in them), but I don't think we can do this as sumif scenarios??

=SUMIF(AND(A13:A47="TV1",H13:H47>0),C13:C47)/COUNTA(H13:H47

Again - any help is greatly appreciated..

----- Aladin Akyurek wrote: ----

=AVERAGE(IF((MOD(ROW(H13:H45)-CELL("Row",$H$13)+0,8)=0)*ISNUMBER(H13:H45),I
(H13:H45>=1,C13:C45))

which must be confirmed with control+shift+enter instead of just with enter

Hi al

value in a corresponding cell is not "#Value" or any other error or numbe
less than 1being nominated with comma's, however I need the formula to evaluate eac
cell separately before it includes it's data

 

[Guest]

Aladin - I was wrong - you formula is adding up great as long as there are no #Value! results in any "H" cells. As soon as it finds those it gives an answer of $0

----- BeSmart wrote: ----


Thanks Frank - but I tried defining the area and doing the formula as
=SUMIF(DP1_ST1_CPT,">0",Station1_DP1)/counta(DP1_ST1_CPT) but I got an answer of #VALUE! - I made sure the defined ranges were the same size too - any other suggestions or did I do this wrong


Thanks Aladin - but I need non-sequential cell addition otherwise the formula will calculate other active cells into the formula - can I still use your formula if I am not selecting a range of cells?

Also, if it helps - I do have a constant label for these rows appearing in column A which we could lookup (ie whether the H cells have #Value! or a number the corresponding A cells always have "Station 1" written in them), but I don't think we can do this as sumif scenarios??

=SUMIF(AND(A13:A47="TV1",H13:H47>0),C13:C47)/COUNTA(H13:H47

Again - any help is greatly appreciated..

----- Aladin Akyurek wrote: ----

=AVERAGE(IF((MOD(ROW(H13:H45)-CELL("Row",$H$13)+0,8)=0)*ISNUMBER(H13:H45),I
(H13:H45>=1,C13:C45))

which must be confirmed with control+shift+enter instead of just with enter

Hi al

value in a corresponding cell is not "#Value" or any other error or numbe
less than 1being nominated with comma's, however I need the formula to evaluate eac
cell separately before it includes it's data

 

[Aladin Akyurek]

Aladin - I was wrong - you formula is adding up great as long as there are

no #Value! results in any "H" cells. As soon as it finds those it gives an
answer of $0?
[...]

The formula includes in the avaerage every 8th numeric value from the
C-range it is given if there is a numeric value >= 1 in the corresponding
H-range. Thus C-values are ignored, which correspond to error values in H.