Well i m kinda new user of Excel and need a lil info regarding V
programing in Excel
My problem is that ( see the attachment ) . i have name Entries ( i
first column ) , their individual percentages ( in second column ) an
average of those individual percentages which belong to same category
differetiated only by last character of their names )
now just focus on second and third column , in this test scenario ,
have manually calculated averages using formula on individual cells bu

if my list goes on quite long almost 3000-4000 name entries . i
becomes hectic to do so .. can there be any VB program to be given i
background . which starts from top , takes in account the names o
cells ( focuses on last character ) , starts from "1" and stops jus
before it strikes another "1" ,calculate average , displays it in nex
column and start repeating this to next block
waiting for positive responce
Regard

+-------------------------------------------------------------------
|Filename: Test.zip
|Download: http://www.excelforum.com/attachment.php?postid=4755
+-------------------------------------------------------------------

--
Dastar
-----------------------------------------------------------------------
Dastard's Profile: http://www.excelforum.com/member.php...fo&userid=3440
View this thread: http://www.excelforum.com/showthread.php?threadid=54184

Your data is naff. You have two PS11341.

Positive enough?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Dastard" wrote in
message ...

Well i m kinda new user of Excel and need a lil info regarding VB
programing in Excel
My problem is that ( see the attachment ) . i have name Entries ( in
first column ) , their individual percentages ( in second column ) and
average of those individual percentages which belong to same category (
differetiated only by last character of their names )
now just focus on second and third column , in this test scenario , i
have manually calculated averages using formula on individual cells but

if my list goes on quite long almost 3000-4000 name entries . it
becomes hectic to do so .. can there be any VB program to be given in
background . which starts from top , takes in account the names of
cells ( focuses on last character ) , starts from "1" and stops just
before it strikes another "1" ,calculate average , displays it in next
column and start repeating this to next block
waiting for positive responce
Regards


+-------------------------------------------------------------------+
|Filename: Test.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4755 |
+-------------------------------------------------------------------+

--
Dastard
------------------------------------------------------------------------
Dastard's Profile:
http://www.excelforum.com/member.php...o&userid=34406
View this thread: http://www.excelforum.com/showthread...hreadid=541843

yeah .. u r right , data is incorrect
lemme alter it ...
Done
check the attachement now
thanks and waiting
Regards


+-------------------------------------------------------------------+
|Filename: Test.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4756 |
+-------------------------------------------------------------------+

--
Dastard
------------------------------------------------------------------------
Dastard's Profile: http://www.excelforum.com/member.php...o&userid=34406
View this thread: http://www.excelforum.com/showthread...hreadid=541843

=IF(RIGHT(A3)="1",AVERAGE($B3:INDEX($B$1:$B$15,
IF(MIN(IF(RIGHT($A4:$A$15)="1",ROW($A4:$A$15)))=0,
MAX(ROW($A$3:$A$15)),
MIN(IF(RIGHT($A4:$A$15)="1",ROW($A4:$A$15)))-1))),"")

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Dastard" wrote in
message ...

yeah .. u r right , data is incorrect
lemme alter it ...
Done
check the attachement now
thanks and waiting
Regards


+-------------------------------------------------------------------+
|Filename: Test.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4756 |
+-------------------------------------------------------------------+

--
Dastard
------------------------------------------------------------------------
Dastard's Profile:
http://www.excelforum.com/member.php...o&userid=34406
View this thread: http://www.excelforum.com/showthread...hreadid=541843

Well i m total dumb . will u plz tell me where should i paste this
formula , i ll appreciate if u use it in my attachment and buzz me back
so that i can see how to use it


--
Dastard
------------------------------------------------------------------------
Dastard's Profile: http://www.excelforum.com/member.php...o&userid=34406
View this thread: http://www.excelforum.com/showthread...hreadid=541843

Just paste it into the first cell, the one with .23 in your example, and
copy down. No VBA.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Dastard" wrote in
message ...

Well i m total dumb . will u plz tell me where should i paste this
formula , i ll appreciate if u use it in my attachment and buzz me back
so that i can see how to use it


--
Dastard
------------------------------------------------------------------------
Dastard's Profile:
http://www.excelforum.com/member.php...o&userid=34406
View this thread: http://www.excelforum.com/showthread...hreadid=541843

Good Morning Dastard, Here is a different take on your code. If your data is
blocked like it is in your example (sorted) you can us this code and get the
same results. Post me back if you have questions, got run get to work.

enjoy, Rick (Fairbanks, AK {Land of the Midnight Sun})


Sub GroupAvg()
Dim LastRow As Long, lRow As Long
Dim ws1 As Worksheet
Dim Top As Long
Dim ZoneStr As String

Set ws1 = Worksheets("sheet1")
With ws1
LastRow = .Range("A3").End(xlDown).Row
lRow = 3 '' <- start row
Do
Top = lRow '' save top of range position
ZoneStr = Left(.Cells(Top, "A"), 2) '' ZoneCode string
Do '' loop thru like zonecode string
lRow = lRow + 1 '' to find last postion of like zone code
Loop While (ZoneStr = Left(.Cells(lRow, "A"), 2)) '' this give bottom
of range (lrow-1)

Range("C" & Top).Formula = "=Average(B" & Top & ":B" & lRow - 1 & ")"
Loop While (lRow < LastRow + 1)
End With

End Sub



"Dastard" wrote in
message ...

yeah .. u r right , data is incorrect
lemme alter it ...
Done
check the attachement now
thanks and waiting
Regards


+-------------------------------------------------------------------+
|Filename: Test.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4756 |
+-------------------------------------------------------------------+

--
Dastard
------------------------------------------------------------------------
Dastard's Profile:
http://www.excelforum.com/member.php...o&userid=34406
View this thread: http://www.excelforum.com/showthread...hreadid=541843

Thanks Rick
tell me how to get in VB mode and where should i copy paste this code
and how to run it i have no idea of using vb in excel .. though i have
worked on VB6 :$
Regards


--
Dastard
------------------------------------------------------------------------
Dastard's Profile: http://www.excelforum.com/member.php...o&userid=34406
View this thread: http://www.excelforum.com/showthread...hreadid=541843

Good evening Daster
First load the spreadsheet that has data you want to figure the Averages
for. Once Loaded Press ALT+F11. This will open Visual Basic Editor of
Excel. Now click on "Insert" on the main menu bar. From the drop down menu
click on "Module", this will insert a new code mode. Now cut and paste the
code I sent you in the last posting, into this code module. Now return back
Excel , by clicking excel Icon below "File" on Main Menu. Now back in Excel
click on ToolsMacroMacros... Now the macro that you copied into code
module will show macro dialog box. Click on it to select, then click the
"Run" Button. I beleive this should get you started. Post back if still have
Questions....

enjoy, Rick



"Dastard" wrote in
message ...

Thanks Rick
tell me how to get in VB mode and where should i copy paste this code
and how to run it i have no idea of using vb in excel .. though i have
worked on VB6 :$
Regards


--
Dastard
------------------------------------------------------------------------
Dastard's Profile:
http://www.excelforum.com/member.php...o&userid=34406
View this thread: http://www.excelforum.com/showthread...hreadid=541843