# excel

Posted by tessa gazzard on November 30, 2001 5:50 PM

right how to best explain i have a list on names in A1 sheet1 about40 entery most names appear more then once some four or five times in the h coloum i have time spent on line for each name. On sheet 2 i have the names again in the A coloum but only one occourance of each name. next i want a colooum with a formuler that will look at the name in A1 sheet 2 say the name was ken jones then i want to be able to go to sheet1 and look for each occourance of that name in the A coloum each time it appears i want excel to average all the corrosponing time online which is displayed in min in coloum H so i end up with average time online for each of the names in sheet 2 coloum A i kind of need a formuler that will average all number in coloum H that corrospond to meeting a certain name
sheet1
a h

ken 10mim
jane 25min
bert 4min
ken 20 min
bert 6min
tess 35min
ken 40min

sheet2
A B
jane average time online
bert average time online
ken average time online
tess average time online
something like this hope you can help i tried with no luck so far

Posted by Russell Hauf on November 30, 2001 6:09 PM

Try this:

=SUMIF(Sheet1!\$A\$1:\$A\$40,Sheet2!\$A1,Sheet1!\$B\$1:\$B\$40)/COUNTIF(Sheet1!\$A\$1:\$A\$40,Sheet2!\$A1)

This assumes that your names on Sheet1 are in cells A1 to A40, and that the minutes are in column B (again to B40), and that the names in sheet2 are in column A also. Enter the above formula in cell B1 (assumes first name is in A1 on sheet2).

Make sense? Hope so!

Russell

Posted by Aladin Akyurek on December 02, 2001 4:16 AM

Getting curious...

> something like this hope you can help i tried with no luck so far

Tessa --

What is wrong? You posted this message much earlier to which I replied (even IML got in the loop). I gave you a formula off-line, which is exactly the same as what Russell Hauf formulates below.

Just curious, that's all.