Basic math question

[Legacy 352679]

Wondering if someone can help me find the most efficient way to figure out a really basic math brain fart I'm having.

Column H has wind speed data.
Column I has the degrees the wind is blowing in.
Column J has the respective wind direction as N, NNE, NE, ENE etc.

How can I group wind speeds together (i.e. 0-10, 10.1-20 etc) to find out the percentage of direction. So, what percentage of wind between 0-10 is blowing N, what percentage of wind between 10.1-20 is blowing N, what percentage of wind between 0-10 is blowing east etc.

 

[DRSteele]

I created this example for you.

MrExcel posts18.xlsx
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
2	observations:	30
3
4	Wind	Compass	Direction		Compass	Direction		Direction \ Wind	0	10	20	30	40	50
5	47	99	E		0.0	N		N	3.3%	0.0%	0.0%	0.0%	0.0%	0.0%	3.3%
6	35	85	ENE		22.5	NNE		NNE	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%
7	54	88	ENE		45.0	NE		NE	0.0%	0.0%	0.0%	3.3%	0.0%	6.7%	10.0%
8	9	358	NNW		67.5	ENE		ENE	6.7%	3.3%	3.3%	3.3%	3.3%	6.7%	26.7%
9	60	56	NE		90.0	E		E	0.0%	0.0%	0.0%	6.7%	6.7%	0.0%	13.3%
10	2	259	WSW		112.5	ESE		ESE	3.3%	0.0%	0.0%	0.0%	0.0%	0.0%	3.3%
11	6	113	ESE		135.0	SE		SE	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%
12	40	341	NNW		157.5	SSE		SSE	0.0%	0.0%	3.3%	0.0%	3.3%	0.0%	6.7%
13	44	167	SSE		180.0	S		S	0.0%	0.0%	0.0%	0.0%	0.0%	3.3%	3.3%
14	45	238	SW		202.5	SSW		SSW	0.0%	0.0%	0.0%	0.0%	0.0%	3.3%	3.3%
15	8	305	WNW		225.0	SW		SW	0.0%	3.3%	0.0%	0.0%	3.3%	0.0%	6.7%
16	27	172	SSE		247.5	WSW		WSW	3.3%	0.0%	0.0%	0.0%	0.0%	0.0%	3.3%
17	69	190	S		270.0	W		W	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%
18	13	68	ENE		292.5	WNW		WNW	3.3%	0.0%	0.0%	3.3%	0.0%	3.3%	10.0%
19	44	318	NW		315.0	NW		NW	0.0%	0.0%	0.0%	0.0%	3.3%	0.0%	3.3%
20	32	92	E		337.5	NNW		NNW	3.3%	0.0%	0.0%	0.0%	3.3%	0.0%	6.7%
21	4	81	ENE						23.3%	6.7%	6.7%	16.7%	23.3%	23.3%	100.0%
22	5	15	N
23	70	69	ENE
24	56	307	WNW
25	39	312	WNW
26	64	55	NE
27	40	70	ENE
28	0	73	ENE
29	25	84	ENE
30	32	51	NE
31	10	239	SW
32	35	109	E
33	65	220	SSW
34	49	103	E
Sheet17

Cell Formulas
Range	Formula
I2	I2	=COUNT(H5:H34)
H5:H34	H5	=RANDBETWEEN(0,70)
I5:I34	I5	=RANDBETWEEN(0,359.9999)
J5:J34	J5	=VLOOKUP(I5,$L$5:$M$20,2,1)
P5:T20	P5	=COUNTIFS($J$5:$J$34,$O5,$H$5:$H$34,">="&P$4,$H$5:$H$34,"<"&Q$4)/$I$2
U5:U20	U5	=COUNTIFS($J$5:$J$34,$O5,$H$5:$H$34,">="&U$4)/$I$2
V5:V20	V5	=SUM(P5:U5)
P21:U21	P21	=SUM(P5:P20)
V21	V21	=SUM(P5:U20)
L6:L20	L6	=L5+45/2

 

[Legacy 352679]

Oh, that looks immensely helpful! When our servers are back up at work I will give this a thorough look. But thank you so much for starting me in the right direction!

 

[Legacy 352679]

G'day @DRSteele,

Edit: I've actually just changed the formula to specify a range rather than a cell value. This works for me now, but if there is a way to make it read from a cell value I would be interested in learning ?

I've finally had a chance to write up everything in a spreadsheet.

Just a follow up question.

With the range in P4:U4 (0, 10, 20 etc), is it possible to change these values to a range itself? I.e. 0-2, 2.01-10, 10.01-14 etc? I've tried doing this but unfortunately it affects the entire table as it no longer recognises those values.

I'll likely seek some help in doing this in VBA but if I can nail your table, then at least I'll have the core setup.

Thank you.

 

[DRSteele]

Well, those ranges you try to put in are just text. The table is working on GREATERTHANOREQUALTO lower number, LESSTHAN upper number. You could put a text field that concatenates that data so that it reads as you wish.

As for VBA I know diddly. I'm trying to figure out how to catch the attention of a VBA expert here.

 

[Legacy 352679]

Well, those ranges you try to put in are just text. The table is working on GREATERTHANOREQUALTO lower number, LESSTHAN upper number. You could put a text field that concatenates that data so that it reads as you wish.

As for VBA I know diddly. I'm trying to figure out how to catch the attention of a VBA expert here.

Yea I think I'm going to have to play around with it a little bit, but by simply changing the formula to read a value rather than a cell, I've managed to get it working nicely (as the 0 - 2 would be used as a title for a wind rose, which is the only reason why I would have it like that).

But thank you for doing all the leg work. It's saved me a lot of time and frustration.