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

J

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
 
B

Barb Reinhardt

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
 
C

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
 
C

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
 
J

Jplink49

Barb, this works. Thanks a lot.

Barb Reinhardt said:
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
 
J

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.
 
J

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top