Formula to compute short form from long form, but need to calculate average values first

Hello,

I have a problem that has stumped me. I would be eternally grateful if someone could help me. I have attached a spreadsheet with a sample of the original data and the solution for which I am hoping to get a formula.

Problem: Averages, sums, and variances need to be calculated and then long form data needs to be collapsed into short form for each expedition. Therefore, I need to calculate the following:
1. average harmony for each expedition
2. sum of “leader” values for each expedition
3. sum of “injury” values for each expedition
4. average age for each expedition
5. variance of age for each expedition

But here is the part I am struggling with: These new values need to be copied into a single row for each expedition. In other words, all the rows for a single expedition need to be collapsed into a single row for each expedition. I have over 50,000 rows of individual data that represent over 8,000 expeditions – that’s why I need a formula to do it for me.

Thank you so much for your help!!! I REALLY appreciate it!

good evening
Let me return the file with line 19-20 in yellow in the formulas to find the desired results.
For the variance in F17-F18 are not very handy with these formulas I made ​​a diagram H2: K11 for the calculation and the
totals are different from yours.
I do not know which formula to use.
Sample data.xlsx

Thank you so much, Berna11 and FDibbins!!! Your help is very much appreciated

I was trying to calculate the variance in age for each expedition. I used the "=VAR.P" formula. Is there a way for me to calculate this for all expeditions?

Hi there,

I have a solution based upon a step in between - workaround - since there isn't a sort of var_if or sth else. However, with the help of an if in combination to offset, the figures have been calculated.
PLease note, that I have set my array up and including to the grey row. If you want the enhance the list, just put the addtional data before that grey, and the formulas are still correctly in place. The only thing you have to do then: copy the if-formula in column H and I .... and things will be fine

When you say "all the expeditions" do you mean all of each of the expeditions?

Formula:

Please Login or Register  to view this content.

returns the results in the upload. It is an array-entered formula, entered in F17 and filled down.

Array enter means the formula must be committed by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.