A B C D
Letter Writer Miro no TM approval Search Time
Advisor 1 No 5
Advisor 1 Yes 5
Advisor 2 No 5
Advisor 4 No 4
Advisor 1 Yes 5
Advisor 3 Yes 6
Hi I need a formual to average colloum D but only when colloum A is the right person
i am doing results for each advisor
so if A = Advsor 1 i need all their times averaged togther in one cell and so on for each advisor i have tried a few of the web all ready and edited to fit my data range but it has not worked.
if you have a list of advisor's then you could use
averageIF()
can you attach a sample sheet showing the layout of your data
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Please find my spreadsheet attached
i am just working on 4.12.13 for those looking and u can see my attempt at the formular that has not worked
Ps please ignor the " " i put them there for another reason not relating to the formular
=AVERAGEIF($A$6:$A$29,A37,$D$6:$D$29) in cell D37 and copy down
would average the search time
if you need other criteria - ie the yes or no
use Averageifs
It's been a while since I used 2003, so Im not sure if this will work, but take a look at using the COUNTIF() function
Also, on your summary sheet, try using this...
=SUM('4.12.13:13.12.13'!B37)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
@ etaf...2003 does not have averageifS()
hi etaf i tried that and it just came up with #Name?
I thought you were trying to count, sorry. Try this...
=AVERAGEIF($A$6:$A$29,A37,$D$6:$D$29)
(not sure if my 2nd range is correct?)
Thanks FDibbins Did not notice the 2003 - sorry for that
if you need to use 2 criteria - ie YES in column C
try
=AVERAGE(IF($A$6:$A$29=A37,IF($C$6:$C$29="yes",$D$6:$D$29)))
use control+shift+enter to enter as an array, {} will appear around the formula
I have used that and i get #Value!
did you enter it using CTRL SHIFT ENTER, and not just ENTER?
yeah i got the {} ethier side
sorry it says #N/A
OK give this a try - adjust ranges as needed...
=SUMPRODUCT((A1:A9=1)*(B1:B9))/SUMPRODUCT(--(A1:A9=1))
sorry i am having no joy
all i need it if row A6 to A 29 = "specific Advisor" and C6 to C 29 = "yes" then i want an average of D 6 to D29 (this colloum contains Times)that matches these two creitera
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks