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

(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

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

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
--
HTH,
Barb Reinhardt



"Jplink49" wrote:

(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

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

On Mar 20, 11:46 am, Jplink49
wrote:
(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

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

On Mar 20, 12:11 pm, cht13er wrote:
On Mar 20, 11:46 am, Jplink49
wrote:





(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- 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

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

Barb, this works. Thanks a lot.

"Barb Reinhardt" wrote:

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
--
HTH,
Barb Reinhardt



"Jplink49" wrote:

(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

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

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.

"cht13er" wrote:

On Mar 20, 12:11 pm, cht13er wrote:
On Mar 20, 11:46 am, Jplink49
wrote:





(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- 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

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

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!



"Barb Reinhardt" wrote:

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
--
HTH,
Barb Reinhardt



"Jplink49" wrote:

(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

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

Try this:

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

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

Dan, that formula worked. Thanks Buddy.

"dan dungan" wrote:

Try this:

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

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

Glad to help