Solved

# Excel Formula Automation

Posted on 2016-08-18
47 Views
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
Question by:FMezler
• 4
• 3
• 2
• +2

LVL 25

Expert Comment

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 28

Expert Comment

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
``````
0

Author Comment

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

LVL 28

Expert Comment

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

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

Why do you have hundreds of tables and where is the underlying data for the percentages?
0

Author Comment

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 17

Expert Comment

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

xtermie: I like the idea, but did you see the sample file I posted? How could I cleanly import that into Access?
0

LVL 25

Accepted Solution

ProfessorJimJam earned 500 total points (awarded by participants)
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

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""))))"
x = tbl.ListColumns.Count
tbl.Range(2, x).FormulaR1C1 = "=SUMPRODUCT(LARGE(" & tbname & "," & tbcolname & ",ROW(INDIRECT(""1:2""))))"
Next tbl

Next sh

End Sub
``````
ExpertExchangeTopBottomExample.xlsm
0

LVL 25

Expert Comment

working answer as per question provided.
0

## Featured Post

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filledâ€¦