Link to home
Start Free TrialLog in
Avatar of Frank
FrankFlag for United States of America

asked on

Excel Formula Automation

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
Avatar of Professor J
Professor J

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"))))
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

Avatar of Frank

ASKER

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
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. :(
Avatar of Frank

ASKER

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.
Why do you have hundreds of tables and where is the underlying data for the percentages?
Avatar of Frank

ASKER

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.
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.
Avatar of Frank

ASKER

xtermie: I like the idea, but did you see the sample file I posted? How could I cleanly import that into Access?
ASKER CERTIFIED SOLUTION
Avatar of Professor J
Professor J

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
working answer as per question provided.