Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing numbers in a list when they meet a critieria in another co
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing numbers in a list when they meet a critieria in another co
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 = On Feb 10, 9:53 am, Russell wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing numbers in a list when they meet a critieria in another co
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing numbers in a list when they meet a critieria in another co
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! ================================================== === "Russell" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display the last non-zero value in a list of numbers | Excel Discussion (Misc queries) | |||
change a vertical list of numbers to horizontal list from 1 cell | Excel Discussion (Misc queries) | |||
Adding numbers from a list over a specific value | Excel Discussion (Misc queries) | |||
find sum in list of of numbers | Excel Discussion (Misc queries) | |||
find sum in list of of numbers | Excel Discussion (Misc queries) |