Solved

Excel Formula Automation

Posted on 2016-08-18
11
81 Views
Last Modified: 2016-09-26
I am not sure if this even can be done but I thought I would ask here since many have helped in the past.

I have an Excel sheet with HUNDREDS of tables (see attached example with only 3) that I need to perform calculations on. The tricky part comes in determining the number of values in each table. If it is a seven-point scale table, I need to find the sum of the top 3 values, the sum of the top 2 values, and the sum of the bottom three values. If it is a five point scale table, I need to find the sum of the top 2 values and the sum of the bottom two values. Any other table size is ignored.

Is there a way to count the number of cells in a range and based on that figure (6 which represents the 5-point scale with total, or 8 which represents the 7-point scale with total) and automatically insert the appropriate formulas for each range of tables?

As I said, the example is three tables, but I have worksheets that have 100, sometimes 200 tables. Right now I just set the formulas for a 7-point table and copy and paste the formulas one by one for every 7-point table and do the same thing for 5-point tables.
ExpertExchangeTopBottomExample.xlsx
0
Comment
Question by:FMezler
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 41761221
it is so easy lets look at the example of your sheet range M5:M10

if you want top 2 use =SUMPRODUCT(LARGE(M5:M9,ROW(INDIRECT("1:2")))) and if you want buttom 2 then use =SUMPRODUCT(SMALL(M5:M9,ROW(INDIRECT("1:2"))))

if you want to change the tops then you can just change the 1:2 to different numbers for example top 3 then =SUMPRODUCT(LARGE(M5:M9,ROW(INDIRECT("1:3"))))
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41761251
See if this works for you....

Sub PlaceSumFormula()
Dim rng As Range
Dim n As Long
For Each rng In Range("I:N").SpecialCells(xlCellTypeFormulas, 1).Areas
   n = rng.Rows.Count
   If n = 6 Or n = 8 Then
   rng.Cells(rng.Rows.Count).Select
      rng.Cells(rng.Rows.Count).Formula = "=SUM(R[-" & rng.Rows.Count - 1 & "]C:R[-1]C)"
   End If
Next rng
End Sub

Open in new window

0
 

Author Comment

by:FMezler
ID: 41761294
I have reattached the worksheet with the areas I need calculated in yellow.

Professor JimJam: While those formulas work, I have HUNDREDS of tables I put them in. I was looking  for something to automatically do them for every table without having to copy and paste formulas all over the place.

Subodh Tiwari: You were the winner the last time for my issue, but this one is different. I am trying to put the numbers in the specific places next to the tables. Specifically (if you open the example I attached) I am looking for the Top 2 number and Bottom 2 numbers for a 5-point table and the Top 3, Top 2, and Bottom 3 numbers for a 7-point table. So in the example I provided, for a 5-point table the Top 2 number would be in cell H5 based on the sum of I8:I9 and the Bottom 2 number would be in H6 based on the sum of I5:I6. A Top 2 number would be in cell L5 based on the sum of M8:M9 and the Bottom 2 number would be in L6 based on the sum of M5:M6. For a 7-point table the Top 3 number would be in cell H16 based on the sum of I20:I22, a Top 2 number would be in cell H17 based on the sum of I21:I22 and the Bottom 3 number would be in H18 based on the sum of H16:H18. The fact that you were able to look at ranges and calculate sums tells me you must be able to do what I am asking.

Thanks for all input!
ExpertExchangeTopBottomExample.xlsx
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41761354
Hmm that's a tricky one and will take lot of time to figure that out. Let me see if I have that much of time. :(
0
 

Author Comment

by:FMezler
ID: 41761368
Yea....that's what I thought. Ever since you provided that other code that did the sums, I have been playing around with the Excel VBA even though I don't really know it. I have programmed in other stuff so I can follow what yours is doing, but I don't know enough to even get close. If you are telling me its tricky, I am not even going to waste my time trying. Thank you for considering and let me know if it can or cant be done. For now I will just keep copying and pasting every set of formulas for all the tables.
0
 
LVL 7

Expert Comment

by:tomfarrar
ID: 41761390
Why do you have hundreds of tables and where is the underlying data for the percentages?
0
 

Author Comment

by:FMezler
ID: 41761502
I have hundreds of tables because I have hundreds of questions and crosstabs. If you look at the sample file I uploaded on my comment (not the one on the original post) you will see the tables automatically generated on the left by a separate program. I export that to Excel and create those columns of percentages using some rounding formulas as well as calculations for top 3, top 2, and bottom 3 boxes. It is the top3, top2, and bottom3 calculations I am trying to automate. I never would have asked if it hadn't been for some code created by Subodh Tiwari regarding a separate question that actually automated an entire autosum calculation process for hundreds of tables so I thought I would at least ask to see if it was possible.
0
 
LVL 18

Expert Comment

by:xtermie
ID: 41762474
If you have hundreds of table, perhaps it is best to import everything into an Access Database and not use an Excel file to do all this data manipulation and calculations in Excel - that's what a database is for, as everything would be done easier with queries.
0
 

Author Comment

by:FMezler
ID: 41767658
xtermie: I like the idea, but did you see the sample file I posted? How could I cleanly import that into Access?
0
 
LVL 26

Accepted Solution

by:
ProfessorJimJam earned 500 total points (awarded by participants)
ID: 41787736
FMezller,

here is the VBA code that will add my formula

into every table of the workbook. meaning on all tables in everysheets of the workbook.

it will create to columns at the end of each table, one will calculate bottom 2 and second one top 2

if you open attached file. see there are many sheets and many tables. simply run the macro by pressing Control SHIFT A and you will see what happens to the tables.

Sub test1()

' author ProfessorJimJam Sep 2016

Dim tbl As ListObject

For Each sh In ActiveWorkbook.Sheets
    For Each tbl In sh.ListObjects
tbl.ListColumns.Add

        x = tbl.ListColumns.Count
 tbname = tbl.Name & "[[#All]"

tbcolname = "[" & tbl.ListColumns(1).Name & "]]"


 tbl.Range(2, x).FormulaR1C1 = "=SUMPRODUCT(SMALL(" & tbname & "," & tbcolname & ",ROW(INDIRECT(""1:2""))))"
 tbl.ListColumns.Add
        x = tbl.ListColumns.Count
 tbl.Range(2, x).FormulaR1C1 = "=SUMPRODUCT(LARGE(" & tbname & "," & tbcolname & ",ROW(INDIRECT(""1:2""))))"
 Next tbl
 
 Next sh
 
End Sub

Open in new window

ExpertExchangeTopBottomExample.xlsm
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 41815688
working answer as per question provided.
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question