I don't even know how to search for this so I will explain my situation and hopefully there is a solution



I have an extensive spreadsheet which lists a number of variables for flaked stone surfaces, eg. quality, type, panel length, scar length, scar width etc.

These variables are recorded based on the individual scars as the rows, so th columns in my Master database looks something like this:

Site Name, Panel Number, Stone Quality, Panel Length, Flake Length (mm), Flake Width (mm) ...

I have over 12 000 entries of this type



I am now wanting to produced a summary table which summaries this info by panel number. Example:

Site Name, Panel Number, Total Scars recorded, Stone Quality, Panel Length, Flake Length (longest), Flake Length (shortest), Flake Length (average), Flake Length (median), Flake Length (SD), ...


There are over 1700 panel entries.



I have recorded the data ranges for the rows of each panel, eg. 8-7BF01 Panel 1 = 2:128, 8-7BF01 Panel 2 = 2:128, 129:193, 8-7BF01 Panel 6 = 206:290.

Currently I have a number of formulas for each row which I copy these data ranges. Below is an example of what I have to do for 8-7BF01 Panel 1.

Total scars =COUNT(G2:G128)
Stone Quality =AVERAGE(K2:K128)
Panel Length =AVERAGE(L:L128)
LxW =AVERAGE(Q2:Q128)
L:W =AVERAGE(R2:R128)
Flake Length (longest) =MAX(O2:O128)
Flake Length (shortest)=MIN(O2:O128)
Flake Length (average)=AVERAGE(O2:O128)
Flake Length (median)=MEDIAN(O2:O128)
Flake Length (SD)=STDEV(O2:O128)
Flake Width (longest) =MAX(P2:P128)
Flake Width (shortest)=MIN(P2:P128)
Flake Width (average)=AVERAGE(P2:P128)
Flake Width (median)=MEDIAN(P2:P128)
Flake Width (SD)=STDEV(P2:P128)

Plus some others which are more complicated
UF1% =COUNTIF(J2:J128,"UF1"), =X4/W3
UF2% =COUNTIF(J2:J128, "UF2"), =Y4/W3



I copy the data ranges for the panels into each formula and edit the column letter to correspond with the section of the data I am wanting to summaris. I then copy this into my summary spreadsheet.

I have to do this for each panel for up to 24 different summary data points. Doing this manually is understandably time-consuming. I am hoping there is a way to speed things up.

I know the formulas I am wanting to use and the row ranges for each panel.

Is there a way automatically populate with the necessary information?

Either by remembering the column info and linking in the data range or some other way?


Cheers