If/then statement that checks multiple columns then averages?

[kixgil]

I'm working on consolidating a data set right now that has 55 subjects with multiple variables per subject to enter into SPSS for regression analysis. I have multiple data points per variable per year, and what I'm trying to do is get the averages for each type of variable per year per subject.

Each year is set to start at 0, and I have 8 variables (phosphorus, nitrogen, dissolved oxygen....etc) that are all text fields. Is there a way to write a function (or macro, just less familiar with those) that has Excel check column A for what year, then column B for which variable, to get one cell that averages the phosphorus total within that one year?

Since I have so many subjects, and each subject has 40 years of data, I'm trying to not do this by hand if at all possible.

Huge thanks to whomever can help with this! Screenshot of example data is below.

[mongoose36]

try

Please Login or Register  to view this content.

[kixgil]

Thanks! I'll try that.

[FDibbins]

Welcome to the forum kixil

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

[kixgil]

So that command is giving me a VALUE error....not entirely sure why. Exact formula: =AVERAGEIFS(AllLAkesData!D2:D104693, AllLAkesData!B2:B104693, 0, AllLAkesData!A2:A104693, "Amnicon", AllLAkesData!C2:CC104693, "TKN"), with column A being the lake name, column D containing the numerical data, column B the recoded year (0 being starting year), and column c being the parameter name. The average range contains only numbers, I have no idea where the error could be. And I get the error every year, and I know I have data for most of the years for this parameter.

Is something in my formula incorrect?

FIGURED IT OUT. Damn thing kept selecting when I wasn't clicking, so an extra letter snuck in. Oif.

[mongoose36]

oops my apologies I got the arguments reversed in my formula.

Please Login or Register  to view this content.