(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

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

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

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

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

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

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

Try this:

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

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

Glad to help