Summing numbers in a list when they meet a critieria in another co

[Guest]

I need to be able to sum numbers in several columns and then calculate the
average of each of these summed columns. But I Only want to sum the numbers
where a 1 exists against that row of data. I have columns of dates and then
the time differences between the various dates in days. The dates signify
dates in a process. I need to calculated the averages of the various time
differences, but only where the whole process is complete. So I have a column
at the end with a "1" in it when that row has all data entered. Example of
what I have is (time diff is guessed here):

Col 1 Col 2 Col 3 Col 4 Col 5 Col 6 Col 7 ..... col 8
1/3/06 2/3/06 1 12/4/06 40 16/4/06 4 1
2/4/06 5/4/06 3 29/4/06 24 5/5/06 6 1
5/6/06 8/6/06 3
0
3/7/06 5/7/06 2 16/7/06 11 20/7/06 4 1
20/7/06 24/7/06 4 29/7/06 5 0


I only what the averages for columns 3, 5 & 7, when there is "1" in Column 8

If there is not a simple way using formulae, can I just sum and then calc
the average by filtering on column 8 - I can't seem to do this either


Russell

 

[mdupris]

You could try this formula in column H:
=IF(H1=1,AVERAGE(C1,E1,G1),0)
and copy it down the column. One issue to watch for is the choice of
'0' as the last parameter. This will be asserted whenever your "IF"
condition isn't met. If zero is a possble real value, you might want
to change it to some impossible average (e.g. -1) just so you know
it's not the real average. If you leave the ',0' off entirely, you'll
get "FALSE" in the cell. It depends on what you're going to do with
the averaged values..

= M =

 

[Don Guillett]

for ONE row
=AVERAGE(IF(H2=1,(C2,F2,G2)))

 

[RagDyeR]

Try this *array* formula:

=AVERAGE(IF(H1:H5=1,C1:C5))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

Just revise the C1:C5 to E and G for averages of the other 2 columns.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


I need to be able to sum numbers in several columns and then calculate the
average of each of these summed columns. But I Only want to sum the numbers
where a 1 exists against that row of data. I have columns of dates and then
the time differences between the various dates in days. The dates signify
dates in a process. I need to calculated the averages of the various time
differences, but only where the whole process is complete. So I have a
column
at the end with a "1" in it when that row has all data entered. Example of
what I have is (time diff is guessed here):

Col 1 Col 2 Col 3 Col 4 Col 5 Col 6 Col 7 ..... col
8
1/3/06 2/3/06 1 12/4/06 40 16/4/06 4 1
2/4/06 5/4/06 3 29/4/06 24 5/5/06 6 1
5/6/06 8/6/06 3
0
3/7/06 5/7/06 2 16/7/06 11 20/7/06 4 1
20/7/06 24/7/06 4 29/7/06 5 0


I only what the averages for columns 3, 5 & 7, when there is "1" in Column 8

If there is not a simple way using formulae, can I just sum and then calc
the average by filtering on column 8 - I can't seem to do this either


Russell