Solved

Excel Formula Automation

Posted on 2016-08-18
11
67 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
  • 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 30

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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 30

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Outlook Free & Paid Tools
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

808 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