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

Can't you upload a file with your data and give examples of expected results?

Ok here is some of my data attached. The tab called 'Master Data' has part of the original dataset. The formulas I am using are located at X2:AE16.

I am using these formulas to populate areas in the tab 'Summary data Panels'. I have populated this spreadsheet once before but had to go back and edit the 'Master Data' so I am currently editing what is already there. The data that I have already edited is highlighted in orange. What is attached is just a small section of my dataset as it is too large to be attached. Column AH has the data range info for each panel I am wanting to summarise .

Attached Files

Flaking data.xlsx (426.1 KB, 7 views)

I regret my ignorance but I simply don't understand.

You write that the formulas you are using are located at X2:AE16 (Master Data) but I can't see what the formulas are referring to (=MAX(#REF!) for example).

Is it formulas in X2:AE16 you want help to or is it something else? If it is what range(s) should the formulas use.

I will do what I can trying to help but I need more help from you to understand what it is that you want.

Oh, I just see that you already have a discussion here:

https://www.excelforum.com/excel-for...he-column.html

Ok I missed that the formulas did not copy over fully when I made this little example spreadsheet. The attached version should have that fixed.

I am trying to summarise some of the data from the 'Master Data' tab into the 'Summary data Panels' tab based on the panel numbers within each site. I have already gone through and made a note of the data ranges for each panel, these are listed in the 'Summary data Panels' tab column AH and the values relate to the 'Master Data' tab.

The formulas that I am using are listed in the 'Master Data' tab X2:AE16.

Using the Data ranges listed in the 'Summary data Panels' tab I copy and paste the relevant data range into the formulas adding the relevant column letter (listed in brackets above each formula). Once these formulas have been completed for a panel (or data range as listed in AH) then they are copied into their relevant positions in the 'Summary data Panels' tab. These positions are highlighted in orange in the 'Summary data Panels' tab.

This whole process is manual. I am looking for a way to automate it a bit. Its not a simple as dragging the corner down as the data ranges for each panel are different. I need a way for the formulas to be locked to a column and then can refer to the AH column to find the data range or some other solution which doesn't have me manually copying and pasting each entry.

I really appreciated your help I have gone through 5000 entries this way and still have 7000 to go I would love any way to speed this up

Attached Files

Flaking data.xlsx (424.9 KB, 7 views)

Is it something like shown in Summary data Panels (2) cells W2:AG4

Attached Files

Flaking data_2.xlsx (422.7 KB, 8 views)

You're a genius! I've been trying to get the suggestion from ExcelForm to work all evening and it just hasn't been working.

Ok so this is an example of one of the formulas:
=MAX((INDEX(FlakeLength,$AH2-1):INDEX(FlakeLength,$AI2-1)))

I noticed that you have split the data range info in my spreadsheet into two columns, and I see how that plugs into the above formula, but how does the formula know that this data range info relates to the 'Master Data' tab?

Because FlakeLength is a defined name. Press Formulas (on the Ribbon), then Name Manager and look how FlakeLength has been defined.

Thanks.
I have gone through and added this formula to all the columns making the edits necessary for them to work. The COUNT function works to count the number of flakes in a panel but for the life of me I can not get the COUNTIF function to work.

I have added Type as a name referring to Column J (Knapping Type) these are recorded as either 'UF1' or 'UF2'. I have added two new columns to get the count of UF1 and UF2 which I will need to calculate their percentages.

This is the formula I've been trying but I can not get it to work. I keep getting the message that there is an error, I was just wondering if you can see what I have done wrong.?

=COUNTIF((INDEX(Type,$AJ2-1):INDEX(Type,$AK2-1)), 'UF1')

Please upload the file and indicate where you have your COUNTIF formulas.

Be aware however that I will not be able to answer until later today (about 8 hours from time of this message).

Not a problem.
I have added the formula to the tab 'Summary data Panels (2)' K2:N2.

I really appreciate your help

Attached Files

Flaking data_3.xlsx (485.2 KB, 7 views)

You will have to use SUMPRODUCT instead of COUNTIF.

See the attached.

Are you aware that you can avoid error messages like the one in L5 by using a formula like:

=IFERROR(2/J5,"") or =IFERROR(2/J5,0)

Attached Files

Flaking data_4.xlsx (424.8 KB, 9 views)

This has solved my problems. Thank you XOR I really appreciate it

If you need more help, please share a copy of your spreadsheet with dummy but realistic data so that the fine members of this forum can take a look. Go through File > Make a copy, then in the new copy of the spreadsheet dummify the contents and follow File > Share > Who has access > Change... > Anyone with the link > Access > Can Edit > Save (note especially the "Can edit" setting.) Then copy the address in the "Link to share" box in the File > Share dialog box and post it here, together with as concrete a description of your scenario and questions as possible.