Average If Adejecent Cells Greater Than Zero
I have a have multiple worksheets that contain data i would like to calculate
some averages for. Down one column is BF, LN, DN, SCK, TOT repeated, and the adjecent cell contains a value I currently have the average for Breakfast as =(SUMIF(August!$J$2:$J$91,"BF",August!$K$2:$K$91))/COUNTIF(August!$J$2:$J$91,"BF"), howver the count function still counts cells that have zero values, I would like the average of only the non-zero cells. Any ideas???? |
Average If Adejecent Cells Greater Than Zero
=AVERAGE(IF((August!$J$2:$J$91="BF")*(August!$K$2: $K$91<0),August!$K$2:$K$9
1)) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "REW2705" wrote in message ... I have a have multiple worksheets that contain data i would like to calculate some averages for. Down one column is BF, LN, DN, SCK, TOT repeated, and the adjecent cell contains a value I currently have the average for Breakfast as =(SUMIF(August!$J$2:$J$91,"BF",August!$K$2:$K$91))/COUNTIF(August!$J$2:$J$91 ,"BF"), howver the count function still counts cells that have zero values, I would like the average of only the non-zero cells. Any ideas???? |
Average If Adejecent Cells Greater Than Zero
That still doesnt work, i played around with it and im still not getting the
average of only the non-zero matching values. It either counts the zero cells as a one to to change the average or it doesnt add them to the sum but it divides by too large of a number. |
Average If Adejecent Cells Greater Than Zero
Never mind, it worked
"REW2705" wrote: I have a have multiple worksheets that contain data i would like to calculate some averages for. Down one column is BF, LN, DN, SCK, TOT repeated, and the adjecent cell contains a value I currently have the average for Breakfast as =(SUMIF(August!$J$2:$J$91,"BF",August!$K$2:$K$91))/COUNTIF(August!$J$2:$J$91,"BF"), howver the count function still counts cells that have zero values, I would like the average of only the non-zero cells. Any ideas???? |
All times are GMT +1. The time now is 07:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com