Forum Discussion

Robert1290's avatar
Robert1290
Brass Contributor
Nov 02, 2021
Solved

VBA to remove rows based on column colour

Hi there,   I have attached an example spreadsheet which I could do with some help on please.   I am trying to acheive the following:-   caluclating the average of each column zz, zy, zx & zw. ...
  • mathetes's avatar
    Nov 02, 2021

    Robert1290 

     

    What you're asking makes sense. What you have as a spreadsheet, however, raises a number of questions.

     

    I've given you a solution. But I made some modifications to the way you arrayed your data, AND to the nature of the data. 

    1. Instead of separating sections by headers, I identified the sections by a new column containing the differentiating label.
    2. And instead of texts that look like numbers but aren't, I used the VALUE function to convert them to values. It's a lot easier to calculate averages (well, for that matter, to do ANY kind of calculation) if you actually use numbers. I'm not sure what the source is for these, but I left them as is and just created adjacent columns with the text converted to value.

    The solution I offered uses a new function, FILTER, to select for the calculation only the rows that meet the criterion of belonging to the designated group. Here's the formula:

    =AVERAGE(FILTER(G$3:G$47,$A$3:$A$47=$M3))

    Where $G3:$G47 is the column containing the values for ZZ (I designated it as ZZ2)

    $A$3:$A$47 is the column containing the group designation

    $M3 is the cell containing the criterion for the filter......

     

    You do need the most current version of Excel for FILTER to function. If you don't have that, I recommend updating your system. This is only one of a number of really useful new functions.

Resources