Variable-Length dependent formula

Hi all-

I have a specific (and I believe, difficult problem) I have an Excel program that does several calculations on a data set that is an input from another program.

Background: The basic gist is that I have a program that varies voltage sequentially and measures the corresponding current. The program then outputs these points in a text file seperated by commas. The user then copies this information into the Excel sheet and runs a macro in order to do all the calculations on the function.

Problem: Previously, all files were in the same format: for example, it swept the voltage from 0V to 0.8V at 100 V/s, giving 800 data points per segment. However, now, some of the data is different (0V to 1.6V sweeps at perhaps 10 V/s, for example, giving 160 Data points per segment). My last problem (there is a cut-and-paste part of the macro that I have figured out how to cut-and-paste variable lengths based upon user input of V/s and Voltage range) is that I have a portion of the program that uses a simple formula for mathematically calculating the integral of the 'function' that is represented by the data points:

(T2-T1)*X1+.5((X2-X1)*(T2-T1))+(The contents of the previous cell)

The problem is that this method relies on there only being a certain amount of data points. Is there a way to adjust it for variable length?

Your choice of Riemann sum to approximate the integral is general enough for any number of intervals. It's called the trapezoidal rule and the formula can be simplified to sum(1/2*(T2-T1)*(X1+X2)). It's general enough to work, so something in the way you are applying it isn't allowing you to be general enough. How are you putting this formula into the spreadsheet? All you really should have to do is extend that formula down the column until it covers the entire data set. Without knowing how you are putting the formula into the spreadsheet, I'm not sure how best to suggest that you extend that procedure.

Hi,

Your formula is analogous to (T2-T1)*(X1+X2)/2 as MrShorty puts it.
If you are entering this formula in the Excel spreadsheet, you have to drag
down the formula all the way to the last row containing data (a shortcut is,
click on the cell containing your formula, move the cursor-pointer to the
bottom right corner -the cursor changes into a skinny plus sign - and
double-click; the column would automatically fill down).

Regards
B.R.Ramachandran

"chris_manning" wrote:

>
> Hi all-
>
> I have a specific (and I believe, difficult problem) I have an Excel
> program that does several calculations on a data set that is an input
> from another program.
>
> Background: The basic gist is that I have a program that varies voltage
> sequentially and measures the corresponding current. The program then
> outputs these points in a text file seperated by commas. The user then
> copies this information into the Excel sheet and runs a macro in order
> to do all the calculations on the function.
>
> Problem: Previously, all files were in the same format: for example, it
> swept the voltage from 0V to 0.8V at 100 V/s, giving 800 data points per
> segment. However, now, some of the data is different (0V to 1.6V sweeps
> at perhaps 10 V/s, for example, giving 160 Data points per segment). My
> last problem (there is a cut-and-paste part of the macro that I have
> figured out how to cut-and-paste variable lengths based upon user input
> of V/s and Voltage range) is that I have a portion of the program that
> uses a simple formula for mathematically calculating the integral of
> the 'function' that is represented by the data points:
>
> (T2-T1)*X1+.5((X2-X1)*(T2-T1))+(The contents of the previous cell)
>
> The problem is that this method relies on there only being a certain
> amount of data points. Is there a way to adjust it for variable
> length?
>
>
> --
> chris_manning
> ------------------------------------------------------------------------
> chris_manning's Profile: http://www.excelforum.com/member.php...o&userid=23986
> View this thread: http://www.excelforum.com/showthread...hreadid=379511
>
>