# Discussion on: Compute your entire Excel worksheet with a click of the mouse

7

### Join the conversation!

View:
Show:
###### Vector Averaging in Excel
phil.jones@... 5th Mar 2008
A pernnial issue - how can you achieve vector averaging (sometimes called geometric averaging etc) to average Wind Direction data, where an arithmetic average for wind directions of 350 degs and 10 degs gives the totally incorrect result of 180 degs.
###### Couldn't you
LocoLobo 5th Mar 2008
set up a formula where if the difference between the angles is greater than 180? then add 360 to the larger angle? Then use a mod function to put it back to 1-360??

I don't think this can be done in a formula in 1 cell. So have a cell that calculates the range. Then next to your raw values transform the value more than 180? lower than the max. Then average and transform back to 1-360?.

Maybe someone else has an elegant solution. I would be interested too.
###### I have absolutley
ewok_village_2000 5th Mar 2008

I'll move on.

Sorry.
###### Try this
LocoLobo 11th Mar 2008
Assume your values to average are in A1:A7

The formula would be

=(MOD(SUM(A1:A7),360)/COUNT(A1:A7))
Moderator
###### Doesn't work in Excel 2K3
NickNielsen 21st Apr 2009
For seven values between 310 and 360, your formula yields a result of 26-something. If you go past 360 to 5 or 10, the result increases to 45.

Somehow, I don't think that's correct.
Moderator
###### Conditionals work well
NickNielsen 21st Apr 2009
But it's not pretty and it only works properly if your prevailing winds are from northerly directions (between 270 and 90).

For the line of data in cell A1, use this formula in cell B1:

=IF(A1 180,A1+360,A1)

For the average, use this formula:

=IF((AVERAGE(B1:B7))360,AVERAGE(B1:B7),(AVERAGE(B1:B7)-360))

Send me a PM and I can send you a sample of the spreadsheet.
###### Vector Averaging in Excel
mjbnet@... 21st Jun 2011
Would like to know where this is going
Keyboard Shortcuts:
Prev
Next
Toggle
###### Join the conversation
Formatting +
BB Codes - Note: HTML is not supported in forums
• [b] Bold [/b]
• [i] Italic [/i]
• [u] Underline [/u]
• [s] Strikethrough [/s]
• [q] "Quote" [/q]
• [ol][*] 1. Ordered List [/ol]
• [ul][*] · Unordered List [/ul]
• [pre] Preformat [/pre]
• [quote] "Blockquote" [/quote]

Join the TechRepublic Community and join the conversation! Signing-up is free and quick, Do it now, we want to hear your opinion.