Average Calculation in Excel VB

[Dastard]

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

 

[Bob Phillips]

Your data is naff. You have two PS11341.

Positive enough?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Dastard]

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

 

[Bob Phillips]

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

 

[Rick Hansen]

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]

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]

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

 

[Rick Hansen]

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 Tools>Macro>Macros... 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]

Its awesome .. its running just the way i wanted it to run .. thanks a
lot rick

Thank you very much

 

[Bob Phillips]

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]

Thanks Bob []
Rick i have few queries now . .just like exception handling .. like
take a look at the attachement now and focus on colourd part , here
data is sorted as it was but block contains just one value .. it should
copy it as it is ... getting me .. take a look i hope u ll get where i m
stuck now
Regards


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

 

[Rick Hansen]

Hey D, It was my pleasure. I'm glad I could help. Have great Day ....

Rick (Fbks, AK)

 

[Rick Hansen]

Hey D, Here is One other thing I was playing with last night with your
project. This macro routine will now sort the data in Columns A & B (set
them up like your data). So new data items can be placed at the bottom of
the list or anywhere in the list. So when this new macro is executed it
Sorts all the data, then break down group and applies the average formula
for each group as the old macro. Just thought I pass this on to ya...

Thanks again for the good words, Rick

=======================================
Sub GroupSortAvg()
Dim LastRow As Long, lRow As Long
Dim ws1 As Worksheet
Dim Top As Long
Dim ZoneStr As String
Dim sRng As Range


Set ws1 = Worksheets("sheet1")
With ws1
LastRow = .Range("A3").End(xlDown).Row
Set sRng = .Range("A3:B" & LastRow)

'' sort first then, group average
sRng.Sort Key1:=Range("A3"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

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