VBA code to find specific data by name. Get average. Put in cell.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jplink49 - 20 Mar 2008 16:46 GMT

(See Sheet Below)I need code that would find the name in C column and get the
Test Result associated with that name in the D column. Do this each time it
sees that name, average the results, and place that result in another
specific cell.

For example!!, find each “PPerson” in range C:2 to C:8 and get corresponding
Result in the D column.
C:4-- 98.7
C:6-- 99.5
C:7-- 99.7

    B    C    D    E    F    G    H
1    Date    Analyst    Results        Analyst    Avg.     Coun
2                    EJackson    98.4    2
3    3/20/08    EJackson    99.2        PPerson    99.3    3
4    3/20/08    PPerson    98.7        MSilverton    99.2    1
5    3/20/08    MSilveton    99.2
6    3/20/08    PPerson    99.5
7    3/20/08    PPerson    99.7
8    3/20/08    EJackson    97.6

Reply to this Message

Barb Reinhardt - 20 Mar 2008 17:08 GMT

You don't need a macro to do this calculation.

Try this:

In G2, put this

=average(if(C3:C8=F2,D3:D8))

Commit with CTRL SHIFT ENTER

Signature

HTH,
Barb Reinhardt

> (See Sheet Below)I need code that would find the name in C column and get the
> Test Result associated with that name in the D column. Do this each time it
[quoted text clipped - 16 lines]
> 7 3/20/08 PPerson 99.7
> 8 3/20/08 EJackson 97.6

Reply to this Message

Jplink49 - 20 Mar 2008 17:35 GMT

Barb, this works. Thanks a lot.

> You don't need a macro to do this calculation.
>
[quoted text clipped - 26 lines]
> > 7 3/20/08 PPerson 99.7
> > 8 3/20/08 EJackson 97.6

Reply to this Message

Jplink49 - 21 Mar 2008 00:31 GMT

Hey Barb

Your formula worked; however what can I add to the formula to handle cases
were the (If) is FALSE. I get that #DIV/0! message. How can I modify it so it
will come back an empty string or even a dash (-). Without getting that
#VALUE!

> You don't need a macro to do this calculation.
>
[quoted text clipped - 26 lines]
> > 7 3/20/08 PPerson 99.7
> > 8 3/20/08 EJackson 97.6

Reply to this Message

dan dungan - 21 Mar 2008 01:00 GMT

Try this:

=IF(ISERROR(AVERAGE(IF(C3:C8=F2,D3:D8))),"",AVERAGE(IF(C3:C8=F2,D3:D8)))

Reply to this Message

Jplink49 - 21 Mar 2008 23:09 GMT

Dan, that formula worked. Thanks Buddy.

> Try this:
>
> =IF(ISERROR(AVERAGE(IF(C3:C8=F2,D3:D8))),"",AVERAGE(IF(C3:C8=F2,D3:D8)))

Reply to this Message

dan dungan - 21 Mar 2008 23:58 GMT

Glad to help

Reply to this Message

cht13er - 20 Mar 2008 17:11 GMT

That's something like this:

private sub makesum()

dim sngResult as Single
dim strTarget as string

strTarget = inputbox("Enter name","Sum Results")

do until cells(icounter,2)="" 'until no more dates
if cells(icounter,3)=strTarget Then
sngResult = sngResult + cells(icounter,4)
end if
icounter = icounter + 1
loop

call msgbox(strtarget & " has a sum of " &
sngresult,vbokonly,"Result")

end sub

cheers

Reply to this Message

cht13er - 20 Mar 2008 17:12 GMT

> > (See Sheet Below)I need code that would find the name in C column and get the
> > Test Result associated with that name in the D column. Do this each time it
[quoted text clipped - 41 lines]
>
> - Show quoted text -

EDIT:

To get the average, simply count the number of times you add to the
sum and divide when you're done .... to put in a cell just do exactly
that :)

Sorry for not reading the question fully :0

Chris

Reply to this Message

Jplink49 - 20 Mar 2008 17:37 GMT

Cht13er,

Thanks for responding to my questions. I'm going to try this code on my test
app. Thanks again, I let you know how it works.

> > > (See Sheet Below)I need code that would find the name in C column and get the
> > > Test Result associated with that name in the D column. Do this each time it
[quoted text clipped - 51 lines]
>
> Chris

Reply to this Message