Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel Formula Automation

Posted on 2016-08-18
11
Medium Priority
?
89 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 27

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 32

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

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 27

Accepted Solution

by:
ProfessorJimJam earned 2000 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 27

Expert Comment

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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
New style of hardware planning for Microsoft Exchange server.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

688 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