How does one apply STDEV to clusters of data in one column by auto generated ID?

[Adam1245]

Hello all,

I've been doing some research and cannot find any info in this area. Here's the bottom line:

Using different tracks of geographical data (decimal degree format) it is possible to calculate the course heading or azimuth of these various tracks.

=IF(C2<>C3,"",IF(C1<>"",IF(C:C=C2,MOD(360+DEGREES(ATAN2((E3-E2),(F3-F2))),360))))

As you an see in the beginning, the formula starts and stops depending on whether or not the ID or track ID (found in a few columns to the left) changes. If the ID repeats then the course heading is calculated. If the ID changes the formula produces a blank in the cell. This allows one to calculate the course heading for several tracks down the same column.

**Hypothetical**
So far in this project the data can be visually displayed as if several hikers started at a given point in a park (with GPS) and went on their own way. The goal is to discern which ones are lost and which ones have found a road and traveled down it. Due to the mass amount of data and very few few resources to hunt down the potential lost hikers it is crucial to find the lost ones quickly as possible which brings me to the issue...

How can standard deviation be calculated for each track down the column and have it populated in each row?

**This would require multiple STDEV calculations guided by the change in track ID and the input of the calculated course headings. The higher the STDEV the more drastic the course deviations. This would allow the user to focus only on the highest prioritized as ordered by this formula.

Please see attached for what has been started. Thanks a bunch for any input!!!!!

 

[r2c2]

@Adam1245
Wow, that is a very unique usage of Excel.

I have two solutions for you.
1. Using Pivots: Give some title to column D. Select all your data. Insert a pivot. Set up track ID as row label and Course as values. Change value field to calculate standard deviation. (Right click on the value field > Summarize values by > More options)
2. If you can't use pivots: Insert ="" at G18 (or whatever happens to the next cell after last Course calculation. Then use below formula in Column H.

=IF(G3="",STDEV(OFFSET(G3,,,MATCH("",G4:$G$18,0),1)),H2)

Hope that pointed you in right direction

 

[Nebu]

Hi:

You can use the following array formula.

=STDEV(IF((C3=$C$3:$C$17)*($G$3:$G$17>0),$G$3:$G$17))

Non-array formula:
=STDEV(IF(MMULT(($G$3:$G$17<>"")*(C3=$C$3:$C$17),1)>0,$G$3:$G$17))

Thanks

 

[Hui]

If you want a simple StdDevIf() function you can apply it like in a MaxIf() function

=MAX(IF(C6:C9="B",D6:D9)) Ctrl+Shift+Enter

=STDEV(IF(C6:C9="B",D6:D9)) Ctrl+Shift+Enter

 

[Adam1245]

Thanks all for the help!!

@Nebu You're formula was the bulk of it. I cleaned up the rest of the STDEV to get rid of the divide by zero error caused by repeats in lats/longs. Course calculations show "" while STDEV displays "N/A".