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.
Aladin
=======
Posted by tessa gazzard on December 03, 2001 2:47 PM
Re: Getting curious...
yes i know you formuler worked great the sumproduct but what i wanted was to know how to do the same thing but avenging the numbers in the coloum this time were before i had to add the numbers that matched the two criteria sorry to be a nusicence acually i aint going to bother with that any more but i would like to know i am picking up so much i spend most of my time here reading everyone post and i just got some great excel books so im on my way no i doing a course and this is for my course work
Posted by Arum Pitt on December 04, 2001 3:10 AM
Could you re-post an English translation, please ?
