Help with excel macro

[jimboo]

i am after some help with a macro in excel. I need the macro to use the count, sum and average function as below. I only want these functions to be carried out at the end of each section, i.e. where the text ends (either map image or small image). I need the macro to be able to recognise where each section starts (i.e. their is text in the first column) and where the section ends (no text in the first column). Then it will carry out the count, sum and average functions under the 3 adjacent columns. Each of the sections has differing amounnts of data, i.e. some like top one below have 3 rows, while others (like the bottom one below) may have 8 rows. So i need the macro to recognise this. I hope this makes sense!! Any help would be appreciated, cheers.

Column 1 Column 2 Column 3 Column 4
Map Image 0.58
Small Image 0.7
Map Image 0.24

count count count
sum sum sum
Average Average Average

Map Image 0.2
Map Image 0.3
Small Image 0.36
Map Image 0.1
Map Image 0.12
Small Image 0.36
Small Image 0.28
Map Image 0.4

count count count
sum sum sum
Average Average Average

 

[larryww]

As to operating on the cells that have contents, use Insert/Name/Define to make range names. In fact, to demonstrate, make your whole column 1 into range Col1, and then run this:

Sub foo()
Dim c As Range
For Each c In Range("Col1&quot
If c <> &quot;&quot; Then MsgBox c.Row, c.Column
Next c
End Sub

You should be able to adapt this for your more specific needs.

 

[SkipVought]

Hi,

You're making it hard. Why do you have breaks in your data? If your data was in ONE CONTIGUOUS TABLE, you could use the Data/Subtotals feature.

So here's what I suggest.
1. Make another column for grouping your data together.
2. Using Data/Subtotals, use the change in the grouping column to do your count, sum & average.

VOLA! Skip,
metzgsk@voughtaircraft.com

 

[jimboo]

i need break in the data because they are presnetations in an experiment. The figures are fixation times on a particular image. So the first group is presentation 1, and there were three fixations. In the second group there were 8 fixations. If they are grouped together i can't see the average fixation times, etc for each presentation, i just get an overall average. The problem i mentioned earlier is that for each presentation there are different amounts of fixations and there are 1200 presentation so i need something to make quicker than by hand.

 

[SkipVought]

Well then, make you PRESENTATION a presentation SEPARATE from your DATA.

Your database should NOT be chopped up like this.

It is BAD design!

This is one of the BIGGEST probelms that users inflict on themselves using spreadsheets. Take it from a life-long information technology professional. Skip,
metzgsk@voughtaircraft.com

 

[jimboo]

its not a bad design its how my eye tracker spurts out the information!!!

 

[SkipVought]

Then you TRANSLATE the raw data into a valid database format from which you will be able to perform a plethora of database functions in order to extract INFORMATION. Skip,
metzgsk@voughtaircraft.com

 

[larryww]

In this situation, jimboo is presented with data that is ... not comma-delimited ... but rather blank line delimited! So jimboo needs logic to parse it.

However, after the parsing, Skip's earlier recommendations can be taken advantage of. So what Skip JUST said, as well as the rest, is a good way to go.