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

[Jplink49]

(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

 

[Barb Reinhardt]

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

Try this:

In G2, put this

=average(if(C3:C8=F2,D38))

Commit with CTRL SHIFT ENTER

 

[cht13er]

(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

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

 

[cht13er]

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- Hide quoted text -

- 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

 

[Jplink49]

Barb, this works. Thanks a lot.

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

Try this:

In G2, put this

=average(if(C3:C8=F2,D38))

Commit with CTRL SHIFT ENTER

 

[Jplink49]

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.

 

[Jplink49]

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!

 

[dan dungan]

Try this:

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

 

[Jplink49]

Dan, that formula worked. Thanks Buddy.

 

[dan dungan]

Glad to help