bizzarre formula needed

[kylefoley76]

i'm trying to write a formula to keep track of how well i concentrate because i'm trying to improve my concentration. in the picture, column a is the date, column b is the time i spend on the activity (the higher the better) and column c is how often i broke my concentration during the activity (the lower the better). column d, e and f condenses the data in column a, b and c. e needs to average the numbers in column b (45) [somehow i need to get 180 to divide by 4] and column f adds up the numbers in column b, then divides that number by the total in column f [180/9]. i will also need somewhere in the formula that $a$2:$a$60=d2 because each day is a summary of the totals in b and c

 

[baodinh]

Try the SUMIF and COUNTIF formulas. If you want the really advanced method, look at creating custom formulas in a Pivot Table.

 

[kylefoley76]

i'm sorry i don't really know what you mean. i need something like in column e

for every cell in A that matches the value in D3 then

if the cell in column c <> (not equal to blank) then add those up and divide by the number of cells that were not blank.

then in column f add up those cells in b for which c was not blank and divide by the number of the sum in cells c.

 

[baodinh]

Using you example, in cell E3, enter the formula "=SUMIF(A$3:A6,D3,B$3:B6)/COUNTIF(A$3:A6,D3)". For F3, enter the formula "=SUMIF(A$3:A6,D3,B$3:B6)/SUMIF(A$3:A6,D3,C$3:C6)"

 

[kylefoley76]

Thanks for your help but i forgot that in the original doc many of the cells in c are blank, so i need to add in a condition

if(c2 <> "")

all the cells in b are full but i only count them if the adjacent cells in c are not blank

 

[baodinh]

What you're proposing won't work with SUMIF and COUNTIF formulas. You'll need to add an additional column or two with what you want to include then modify the SUMIF and COUNTIF formulas to use that range instead.

 

[Aladin Akyurek]

Thanks for your help but i forgot that in the original doc many of the cells in c are blank, so i need to add in a condition

if(c2 <> "")

all the cells in b are full but i only count them if the adjacent cells in c are not blank

Care to provide a sample for which the suggestions baodinh makes would not work? Please also provide the desired results.