Average Data from Multiple Excel Log Files

Click For Summary

Discussion Overview

The discussion revolves around extracting data from multiple Excel log files to calculate an average from a specific cell across these files. The focus is on finding an efficient method to automate this process, particularly through programming in VBA.

Discussion Character

  • Technical explanation
  • Homework-related

Main Points Raised

  • One participant seeks an efficient method to average data from a specific cell in multiple Excel spreadsheets with the same structure.
  • Another participant suggests writing a macro in VBA as a potential solution and inquires about the original poster's experience with programming in VBA.
  • The original poster expresses a lack of experience in VBA but is willing to try it based on the suggestion.
  • A participant notes that if the spreadsheet names are sequential, it would facilitate the creation of a do-loop in the macro to increment through the file names.
  • The original poster confirms that the spreadsheet names are indeed sequential and mentions looking at a VBA tutorial.

Areas of Agreement / Disagreement

Participants generally agree on the feasibility of using VBA for this task, but there is no consensus on the specific implementation details or the original poster's ability to program in VBA.

Contextual Notes

The discussion does not address potential limitations of using VBA, such as dependencies on Excel versions or specific configurations of the log files.

antonantal
Messages
242
Reaction score
21
I have a lot of Excel spreadsheets which are actually log files from a test (so they all have the same structure) and I need to get the data (a number) from a specific cell (same cell) in each file and do their average.

Does anybody know an efficient method to do this?
 
Physics news on Phys.org
I would try writing a macro to do this. Have you done any programming in VBA (the language of Excel and Word macros) yet?
 
No I have never programmed in VBA. But if you say that it can be done using VBA I'll give it a try.
 
It's not hard to do if you've programmed in other languages.

It would help if the spreadsheet names are sequential in some fashion, for example:

log001.xls
log002.xls
log003.xls
etc. etc.

Then you can write a do-loop where the spreadsheet name increments each time through the loop.
 
Redbelly98 said:
It's not hard to do if you've programmed in other languages.

It would help if the spreadsheet names are sequential in some fashion, for example:

log001.xls
log002.xls
log003.xls
etc. etc.

Then you can write a do-loop where the spreadsheet name increments each time through the loop.

Yes, the names are sequential. Thanks for the suggestion. I am looking right now over a VBA tutorial.
 

Similar threads

Which software to use to create this graph?
  • · Replies 3 ·
Replies
3
Views
4K
MATLAB Solving MATLAB to Excel Copy Issues
  • · Replies 18 ·
Replies
18
Views
6K
MATLAB How can I append new columns to an existing Excel file with MATLAB?
  • · Replies 2 ·
Replies
2
Views
5K
Mathematica Understanding the Code: How to Extract Data from a Plot in Mathematica
  • · Replies 1 ·
Replies
1
Views
3K
MATLAB Export Standardized Matlab Data Points To Excel
  • · Replies 2 ·
Replies
2
Views
2K
Should Excel macros self-adjust when you delete lines above the affected cells?
  • · Replies 27 ·
Replies
27
Views
4K
Fixing Annoying Word/Excel Windows Problem
  • · Replies 3 ·
Replies
3
Views
3K
MATLAB Exporting data points to Excel with Matlab?
  • · Replies 2 ·
Replies
2
Views
2K
MATLAB MATLAB GPU Cross-Correlation Code Fails with OOM on 8 GB Supercomputer
  • · Replies 4 ·
Replies
4
Views
7K
"Allow editing" absent in sent EXCEL file
  • · Replies 13 ·
Replies
13
Views
2K