Identify position based on voltage data

[artz]

Hi,

I am using an accelerometer to determine a patient's position on a bed. Five positions need to be identified. In the table below, corresponding accelerometer voltages were measured in each of the five positions.

Orientation	Vx	Vy	Vz
Supine flat	1.666	1.68	1.007
Supine 10˚	1.657	1.88	1.036
Prone     	1.656	1.681	2.34
Left side	1.671	1.009	1.615
Right side	1.687	2.325	1.675

Can anyone in the Forum identify the logic needed where the inputs would be Vx, Vy, and Vz and the ouput would be position?

A nested if statement would seem to be the way to do this or with VBA. Any help is greatly appreciated.

Thanks,

Art

 

[artz]

Hi kweaver,

The data are part of a study looking at sleeper position over the course of a sleep night. Accelerometer data are digitized by an Adafruit DAQ for next day analysis. Only the five positions described are being considered.

You used a 2% window; this could probably be an input variable. Also, positions returning voltages outside the values described could return, "No Position". This could change, but for now, starting with the nominal voltages and a tolerance that can be varied should suffice.

So, yes, each row of Vx, Vy, and Vz would be tested for the criteria and a value for position would be returned with the "No Position" value returned as described as above.

Does this help to explain what I need? Certainly a VBA solution seems preferable.

Thanks,

Art

 

[kweaver]

Not really. I guess I'm being somewhat dense here. Correct me, of course, if I'm wrong...
But, are you saying that there would be SEVERAL triples on the input side, each of which would show a position?

 

[kweaver]

Will this adjustment do it?

Excel 2010
	A	B	C	D	E
1	Orientaton	Vx	Vy	Vz
2	Supine flat	1.666	1.68	1.007
3	Supine 10"	1.657	1.88	1.036
4	Prone	1.656	1.681	2.34
5	Left side	1.671	1.009	1.615
6	Right side	1.687	2.325	1.675
7	Tollerance	2%			Position
8	Input:	1.68	2.3	1.69	Right side
9		1.655	1.89	1.035	Supine 10"
10
11
12
13
14
15
VoltageData

Cell Formulas
Range	Formula
E8	{=IF(ISBLANK(B8),"",IFERROR(INDEX($A$2:$A$6,MATCH(1,($B$2:$B$6<=B8*(1+$B$7))*($B$2:$B$6>=B8*(1-$B$7))*($C$2:$C$6<=C8*(1+$B$7))*($C$2:$C$6>=C8*(1-$B$7))*($D$2:$D$6<=D8*(1+$B$7))*($D$2:$D$6>=D8*(1-$B$7)),0),1),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[artz]

Hi kweaver,

The input (this is just to ID inputs/outputs, not in actual worksheet)

This is an abbreviated data table that would returned in the data acquisition file. The output column would be returned when the input columns are processed in Excel.

Can you come up with some code to process the input data? I think this would require VBA, but I don't know.

Thanks,

Art

Input	Input	Input	Input	Output
Time	Vx	Vy	Vz	Orientation
0	1.666	1.68	1.007	Supine flat
1	1.666	1.68	1.007	Supine flat
2	1.666	1.68	1.007	Supine flat
3	1.666	1.68	1.007	Supine flat
4	1.666	1.68	1.007	Supine flat
5	1.666	1.68	1.007	Supine flat
6	1.657	1.88	1.036	Supine 10˚
7	1.657	1.88	1.036	Supine 10˚
8	1.657	1.88	1.036	Supine 10˚
9	1.657	1.88	1.036	Supine 10˚
10	1.657	1.88	1.036	Supine 10˚
11	1.657	1.88	1.036	Supine 10˚
12	1.657	1.88	1.036	Supine 10˚
13	1.657	1.88	1.036	Supine 10˚
14	1.656	1.681	2.34	Prone
15	1.656	1.681	2.34	Prone
16	1.656	1.681	2.34	Prone
17	1.656	1.681	2.34	Prone
18	1.671	1.009	1.615	Left
19	1.671	1.009	1.615	Left
20	1.671	1.009	1.615	Left
21	1.671	1.009	1.615	Left
22	1.671	1.009	1.615	Left
23	1.687	2.325	1.675	Right
24	1.687	2.325	1.675	Right
25	1.687	2.325	1.675	Right
26	1.687	2.325	1.675	Right
27	1.687	2.325	1.675	Right
28	1.687	2.325	1.675	Right

 

[kweaver]

I think my last example w/2% tolerance did it, yes/no?

Excel 2010
	A	B	C	D	E
1	Orientaton	Vx	Vy	Vz
2	Supine flat	1.666	1.68	1.007
3	Supine 10"	1.657	1.88	1.036
4	Prone	1.656	1.681	2.34
5	Left side	1.671	1.009	1.615
6	Right side	1.687	2.325	1.675
7	Tollerance	2%			Position
8	Input:	1.666	1.68	1.007	Supine flat
9		1.666	1.68	1.007	Supine flat
10		1.666	1.68	1.007	Supine flat
11		1.666	1.68	1.007	Supine flat
12		1.666	1.68	1.007	Supine flat
13		1.666	1.68	1.007	Supine flat
14		1.657	1.88	1.036	Supine 10"
15		1.657	1.88	1.036	Supine 10"
16		1.657	1.88	1.036	Supine 10"
17		1.657	1.88	1.036	Supine 10"
18		1.657	1.88	1.036	Supine 10"
19		1.657	1.88	1.036	Supine 10"
20		1.657	1.88	1.036	Supine 10"
21		1.657	1.88	1.036	Supine 10"
22		1.656	1.681	2.34	Prone
23		1.656	1.681	2.34	Prone
24		1.656	1.681	2.34	Prone
25		1.656	1.681	2.34	Prone
26		1.671	1.009	1.61	Left side
27		1.671	1.009	1.61	Left side
28		1.671	1.009	1.61	Left side
29		1.671	1.009	1.61	Left side
30		1.671	1.009	1.61	Left side
31		1.687	2.325	1.67	Right side
32		1.687	2.325	1.67	Right side
33		1.687	2.325	1.67	Right side
34		1.687	2.325	1.67	Right side
35		1.687	2.325	1.67	Right side
VoltageData (2)

Cell Formulas
Range	Formula
E8	{=IF(ISBLANK(B8),"",IFERROR(INDEX($A$2:$A$6,MATCH(1,($B$2:$B$6<=B8*(1+$B$7))*($B$2:$B$6>=B8*(1-$B$7))*($C$2:$C$6<=C8*(1+$B$7))*($C$2:$C$6>=C8*(1-$B$7))*($D$2:$D$6<=D8*(1+$B$7))*($D$2:$D$6>=D8*(1-$B$7)),0),1),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[kweaver]

Since they're all exact, you can do 0% tolerance.

 

[artz]

Hi kweaver,

Yes, your last formulary works great. Thanks! It appears that you are also handling blank cells- this is good. A few questions remain:

Can a similar formula be done without an array formula? Or can the array formula look for the last used row? With the array formula, I need to allocate at least or more rows that contain data. Also, array formulas can be finicky if you change something.

I mentioned in my last post returning a message in the corresponding row cell if the voltage values don't match the prescribed x, y, z value ranges.

Is there a way to address this?

Thanks,

Art

 

[kweaver]

I'm not sure why you don't feel right about the array formulas. I see no issue with them.
As for returning a message when there isn't a match: that can be done by changing E7 to the following, and filling down:

=IF(ISBLANK(B8),"",IFERROR(INDEX($A$2:$A$6,MATCH(1,($B$2:$B$6<=B8*(1+$B$7))*($B$2:$B$6>=B8*(1-$B$7))*($C$2:$C$6<=C8*(1+$B$7))*($C$2:$C$6>=C8*(1-$B$7))*($D$2:$D$6<=D8*(1+$B$7))*($D$2:$D$6>=D8*(1-$B$7)),0),1),"No Orientation"))

 

[artz]

Hi kweaver,

Thanks so much, your formula works great! The reason I generally don't like array formulas if that if you mess up part of the formula in a cell, you receive the message: "You can't change part of an array". The only way that I found to get out of that was to shutdown Excel from the Task Manager. So, that's my beef with array formulas.

So, now it's time to build some hardware, collect some data, and try out your formula.

Thanks again,

Art

 

[kweaver]

Did you get the workbook I sent you via PM?