find average of column depends on adjacent column

[MaxXx]

Hello,

I'm trying to make a VI where it would takes data from an excel file and find the average of a second column depending on the range of the values of the first column. For example: I want to find the average of column B when column A is in the range of 5 increment

Input                        Output

A       B                     A          C

0       3.1                  0          3.55

0       4

5       2.3                  5          3.3

5       3.4

8       4.2

10     7.6                  10        7.775

10     7.1

10     8.3

11     8.1

I dont know how to make it so that it would automatically determine the range. I could find the average of the second column but it will just average everything and not in the 0-5 increment. Any help would be greatly appreciated

Thank you,

Max

Message Edited by MaxXx on 11-16-2009 06:59 PM

[vt92]

This is probably an over-complicated solution, but it will work.  (See the attached vi)

Message Edited by vt92 on 11-17-2009 08:40 AM

[MaxXx]

Thank you for your help. I have modified your VI a little to add the read from excel spreadsheet. It does seems to calculate the average for the first few group (0-50) in column A but after that it seems to output some random average not in the interval.  I have attached my spreadsheet file which contains the original voltage, current as well as the hand-calculated average current. There are ~25000 data points, so could it be because there are too many data points?

Thank you,

Max

[altenbach]

Is the first column always sorted?

[MaxXx]

Yes. It is a recorded voltage, so it would be increasing from 0V to 2000V by 50V increment. Sometimes the power supply voltage output is not perfect so it would be like 49.99V instead of 50V, which I think causes the VI to miscount and compute the wrong average. Basically I'm just trying to find the average current at approximately every 50V increment, whether it be 49.99 or 98.99V.

[altenbach]

Try something like this. (Just a quick draft in LV 8.6. Please verify correct operation).

(Anyway, there are better ways to get a column than to use transpose and a FOR loop! Try e.g. "index array" ;))

[altenbach]

---

altenbach wrote:

(Anyway, there are better ways to get a column than to use transpose and a FOR loop!)

---

See also....

[MaxXx]

Thank you for your help. The VI currently counts 49.9 as within the 50 range and calculate the average of the current from 0-50V. I've been trying to modify the VI so that it would count 49.9V or 50.2V as 50V (acceptable tolerance of +/-1) and average the corresponding current; but it's not working.  If you could, please have a look to see what I did wrong.

Thank you,

Max

Message Edited by MaxXx on 11-23-2009 06:30 PM

[Darin.K]

If you look at your In Range and Coerce Function you will see that it will always pass the number straight through.  x is always in the range x-1 to x+1.  You could use the floor function (Round to -Infinity) to get 50.x to count as 50.  I am not sure it makes sense, since your range is now 0-50.999,51-100.999.., etc.  but if that is what you want, that is one way to get it.  To avoid confusion you could specify the exact ranges you want and then we can give you better advice.  For example

[0,51)

[51,101)

[] means inclusive, () means not inclusive

[MaxXx]

Sorry this is a little hard for me to explain. When I take a voltage reading, it's not always a perfect even number, sometimes it outputs 49.9 and sometimes 50.2. I want the VI to treat those number as the next interval. I'm trying to make the VI so that it knows 49.9 is within 50+/-1V tolerance, therefore it will not average all the values of the second column for [0,49.9], but rather two separate stopping points (average of [0] only, and average of [49.9]).  Furthermore, sometimes there are values in between like 30.5, this should be included in the average of 0 since it is way below the 50+/-1. The way it is now, it just averages everything on the second column for [0,49.9].

Max

Message Edited by MaxXx on 11-24-2009 01:46 AM