Help doing a calculation in Excel

[gauchogolfer]

Hi all,
I have quite a bit of data I'm trying to reduce in Excel, and I'm having some trouble coming up with a straightforward way to do it. Perhaps I'll need to break out Matlab or something if required. Here's what I'm trying to do:

I have data in the following table:



I want to calculate the median and stdev of the resistance for each sample. Resistance is (Voltage1-Voltage2)/(Current1-Current2).

I imagine this requires a macro of some sort, but I'm not a VBA wizard, thus thinking about going the Matlab route if required.

This is just a subset of my data, in the actual case I've got between 5000 and 7000 rows to reduce.

Thanks for any help.

 

[MisterMe]

OK. Your data are nonsensical and your analysis is fatally flawed. Resistance is the ratio of voltage to current. If you suspect a bias in your measurements, then the proper analysis is to do a linear regression of voltage vs. current. The linear coefficient will determine your resistance.

However, you have only two voltage values and several measures of current to one significant figure. It makes no sense to repeat the same voltages over and over again. I took the liberty of analyzing your data. Your Sample 1 has a resistance somewhere around 300 ± 40 ohms. Your Sample 2 has a resistance of 280 ± 45 ohms. Both results were calculated using linear regression of V vs. I. With the small number of data points, your data cannot show a statistical difference between them.