G’day Excel Forum
I’ve been having a bit of trouble generating a horizontal multi-conditional product and/or average for the attached spreadsheet.
I need to be able to generate an average or product (depending on the 'Method' setting in cell D12) for row 17 of every nth column (The W value for each variable) for which the S/T value above equals either "S/T" or the letter code above the equation ('T' for E13 or 'S' for F13) and the 'Status' setting is 'A' (signifying Active rather than I signifying Inactive).
I can calculate the product/average for every 5th column with ease using the equation in cells C15 and D15 however I cannot figure out how to adjust this equation to consider only the W values for which these two conditions are met ("S/T" and "Status").
Generating a customized average/product for each analysis (see cells D21 through E22) would be impractical as the number of variables in consideration for each would be highly fluid and can number up to 100 (500 columns) requiring rewriting the equation for each. Hence the need for this customized version.
Please note that I have included a number of values here that have nothing to do with the equation I'm looking for (pale blue and pale grey text), these have been included only to provide an idea of the structure that the final product will take.
I know this sounds complicated, I’ve tried to simplify it as best as possible in the attached spreadsheet.
If anyone has any idea how to resolve this one, please give us a yell
Cheers
MattRNR
Bookmarks