Automatic calculation of the average values in alternating columns.

  • Hello everyone,


    I’ve code (thanks to Saurabhjaina211), that inserts blanc columns between columns with data and makes calculations. I still have to manually make calculations of the average value at the bottom of the columns with percentage values. I was wandering if it is possible to automatically calculate average percentage of the values of the alternating columns with data in percentage format. The number of rows as well as number of columns are flexible, alternating columns start from column E. Would be grateful for any help.

    P.S. Please find sample of the table as an attachment to this post.

    Dilshod


  • The simplest solution is to use an Excel Built-in Table with a Totals Row rather than a manually created table.

    Files

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • The simplest solution is to use an Excel Built-in Table with a Totals Row rather than a manually created table.

    This table is not created manually, the code published is part of the code that copies and transposes data from one table to another one, to simplify explanation I decided to publish part of the code. I know how to calculate totals in Excel for a single column, but I do not know how to do that for multiple alternating columns.

  • What I meant by "manually created" was a table of data that was not in an Excel Built-in, the fact that code was used to make it makes no difference.


    If, however you do not wish to use Excel Built-in Tables then this will remove any Total Averages that are already there and add new ones.


    Files

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • What I meant by "manually created" was a table of data that was not in an Excel Built-in, the fact that code was used to make it makes no difference.


    If, however you do not wish to use Excel Built-in Tables then this will remove any Total Averages that are already there and add new ones.

    Hi KjBox,


    Thanks very much for your help. Though there might be an error in your formula as there is quite a big discrepancy between manually calculated averages (if we assume formula is =AVERAGE(E2:E361)) Excel calculation for the column E gives 3.79% and result of your VBA code is 8.85%, the same is with the rest of the column results. Thanks for your time anyway.

    Dilshod

  • Sorry my fault. Try changing the code to this


    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • You're welcome. Thanks for the "Like"

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • You're welcome. Thanks for the "Like"

    Hello KjBox,


    Sorry its me again. It gives error message (Type mismatch, Line#12 of the code) with another seemingly identical table.

    Is there any chance you could help me to figure out reason for that error?

    Please find attached file with Sheet2.

    Thanks in advance,

    Dilshod

  • The error is because the last column (Column AK) has no data for %Change.


    I have included a bit of code to account for that.


    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.