Im trying to calculate the total sound level of about 500 data points (each point is every 4 columns in my spreadsheet), and I have the following formula to guide me there: SPL-Addition03.gif, where LΣ = Total level and L1, L2, ... Ln = sound pressure level of the separate sources in dBSPL.
Is there a quick macro or Excel feature that can help me implement this?
I'm not aware of a single click solution to this problem already programmed into Excel. The quickest/easiest way I see of doing this looks like this:Is there a quick macro or Excel feature that can help me implement this?
I've almost always found, especially with series sums like this, that the easiest way to put them in Excel is to get the individual elements of the series into a single continguous range. Since your data are kind of spread out, I would first bring the data into a contiguous range, then apply the operations to each element, and finally, sum them all up. My solution might look like this (comma delimited):I don't know if that will meet your definition of quick and easy (it often seems to me that "quick and easy" means "take a lot of time and effort trying to derive a single cell array formula that will do all of the above"), but it seems quick and easy to me.
Please Login or Register to view this content.
Last edited by MrShorty; 11-21-2013 at 02:50 PM.
Originally Posted by shg
Cant I use the following code, and then just change it so that instead of adding columns, I select a specific box and move on until I get to the range of columns that I need...and then just apply those values into my equation. Then once it calculates for the first line, it goes down each row until some max? I know this might be inefficient, but I need a fast solution to just get it to work.
The only other idea I had to throw around was to select data every 4 columns, for all rows and then input that into a chart. It would make for a lot more data Id have to plot, but it might make operations easier?Please Login or Register to view this content.
Try and see if it gives you what you want, you can always "undo" or reopen without savingCant I use the following code, and then just change it so that instead of adding columns
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Well I tried using
but that threw me a run time error when I tried to run it. Is my notation correct?Please Login or Register to view this content.
Hide is not a method, Hidden is a property:
Please Login or Register to view this content.
Entia non sunt multiplicanda sine necessitate
If you want to adapt VBA code to this problem, I don't see anything wrong with that. Perhaps a lot depends on which approach you feel will be easiest. As I noted above, I have found that the easiest way to deal with many operations in a spreadsheet is to group data into contiguous ranges, then operate on those ranges, so my suggestion was based on my opinion of what is easier. Many find coding VBA (or other symbolic programming language) is easier than spreadsheet formulas, and they will, like you are suggesting, use their favorite programming language to perform calculations. In many ways, it is a matter of personal preference and which "language" (treating a spreadsheet as a programming language) you find it easier to program in.
Which do you find is easier for you: spreadsheet formulas or VBA code?
Well after numerous attempts, I cant seem to get the code correct to hide the columns I want. It seems to be haphazard in the first few columns when the macro starts, and then from there it hides the wrong columns.
Here is my code:I have data on column 1469 that I want to skip, then hide the three columns to the left of it, skip one column and keep going back and forth.Please Login or Register to view this content.
If I can just get the column hiding to work, then I can move on to figuring out that log summation of the data all in sequential order.
That is what Im trying to do: group the data into one long continuous range. Easiest way is to first hide the data that is in between, and then run the mathematical operation on the range.
This is not the kind of VBA programming I do, so I'm not very good at it.
Since the pattern is show every 4th column, it might be easier to hide all 1500 columns, then, start with the first (or last) column you want to show, and step through and show every 4th column from there.
One caution -- I'm not sure if Excel's worksheet functions automatically ignore hidden cells. You might go to all this work to hide columns, then find that the =sum() function is still including the hidden functions in the summation. I would certainly suggest you test this to see if hiding columns causes them to be ignored by the sum function or not.
=SUM(A1:Z1) will include hidden cells within that range.
=SUBTOTAL(109, A1:Z1) will NOT include hidden cells within the range.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
I do believe it will, JB. It's one of those row/col asymmetries in Excel.=SUBTOTAL(109, A1:Z1) will NOT include hidden cells within the range
=SUBTOTAL(9, A1:Z1) will include hidden rows, =SUBTOTAL(109, A1:Z1) will not
There's only one row referenced in the formula, FD. Did you try it?
He's right, Ford. Excel inconsistencies rears it's ugly head again.
The SUBTOTAL(109, Range) will NOT include hidden ROWS within a vertical range, but it WILL include hidden columns across a horizontal range.
i stand corrected, thanks
Couple of functions I have in my personal.xls...
Please Login or Register to view this content.
This is a great bit of code. Any chance you have an example that uses it so I can see it in action?
Also, this all assumes that Ive managed to hide all of the columns in between the dB data in order to analyze just that data sequentially. Any code ideas for doing that? I tried doing it manually yesterday, and it took an hour just to get through about 50 data points - Ive got 500.
Last edited by Science-Guy; 11-22-2013 at 05:50 PM.
if you have
80 in A1:A4
=dbasum(A1:A4)
will return 86
=dbaaverage(A!:A4)
will return 80.
If you want only visible cells then change lines that look like:
toPlease Login or Register to view this content.
Please Login or Register to view this content.
I got together with an in-house programmer from another department and we figured this out. Thanks for the help!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks