Find averages for # of events (Column B) & related averages for days 1-7 in Column C

[thankyou]

Hi, hope you're doing great today.

In column B, I have # of events, like this:

[TABLE="class: cms_table, width: 65"]
<tbody>[TR]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]


In column C, I have Day number of week, eg:
[TABLE="class: cms_table, width: 106"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD]1 (Sun)
2
3
4 (Wed)
5
6
7 (Sat)[/TD]
[/TR]
</tbody>[/TABLE]


In column D (if you want to use this instead of day number), I have the actual weekday name (eg TUE, etc).

Here's where I need your help, please:

I want to make a table (results in H2 thru N5), like this:

[TABLE="class: cms_table, width: 468"]
<tbody>[TR]
[TD][/TD]
[TD]MON[/TD]
[TD]TUE[/TD]
[TD]WED[/TD]
[TD]TH[/TD]
[TD]FR[/TD]
[TD]SAT
[/TD]
[TD]SUN
[/TD]
[/TR]
[TR]
[TD]AVERAGE # of events[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MODE1(most frequent event #)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MODE2 (2nd most frequent event #)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MEDIAN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


So, if possible, I'd like the formulas ideally, for my learning stage, that will go into H2 thru M5 for my output table.
Thank you very much for your help and time, which I truly value and appreciate. If can't do formulas I can try arrays, but I always forget when I go back into edit, and end up hitting Enter instead of CSE.
(oh, by "mode2", i mean 2nd most frequent occurrence from column B which is the data I'm hoping to analyze).

thank you so much!

 

[XOR LX]

Hi again!

Just one question: Excel can recognise the text strings "Thu" and "Fri" as referring to those days, though not (without prior manipulation) "Th" and "Fr".

You've used recognisable 3-letter abbreviations for all the other days of the week - could you do so also for these two, which would make solutions a bit more consistent? Or must those two stay as 2-letter abbreviations?

Cheers

 

[thankyou]

Absolutely. Either way, 2 or 3 letter abbrevs, would be fine. Thanks so much.

 

[XOR LX]

How are your days of the week stored in column D?

Are they simply text entries, or perhaps generated by some formula and in cells formatted as e.g. "ddd"?

Regards

 

[thankyou]

Column D weekday numbers are generated indeed from a formula of values in column A like this:

Column A ... Column D

2/10/14 MONDAY

"MONDAY" is generated from column A using "=WEEKDAY(A2)".

Thanks for your post.

Have an awesome day.

 

[AhoyNC]

I don't know of any easy way to get your mode2, but maybe this will help you get started with the other parts of your table.
Just copy the formulas in column I of the example across. Be careful how you lock the cell references. Notice that in the COLUMNS $I$1:I1 that the first cell is lock and the other is not. This will give you an expand range as you copy across, so one cell to the right would become COLUMNS $I$1:J1. This is just a counter that will match up to the Day of the Week # in your column C.

The formulas for the MODE and MEDIAN are array formulas and must be entered with

Excel Workbook
	A	B	C	D	H	I	J	K	L	M	N	O
1		Events	Day of Week			SUN	MON	TUE	WED	TH	FR	SAT
2		2	1		AVERAGE # of events	2.7	1	1	3.5	1	2.5	0
3		1	2		MODE1(most frequent event #)	2	1	1	None	1	None	None
4		1	3		MODE2 (2nd most frequent event #)
5		2	4		MEDIAN	2	1	1	3.5	1	2.5	0
6		1	5
7		4	6
8		0	7
9		2	1
10		1	2
11		1	3
12		5	4
13		1	5
14		1	6
15		4	1
CTRL-SHIFT-ENTER.

 

[thankyou]

AhoyNC,

Excellent. Let me get to work on that. I'll get back to you shortly.

Thanks and kind regards,

Bob

 

[thankyou]

AhoyNC,

Simply put, it's PERFECT.

Thank you so much for your awesome help. I'm learning a lot.

I'm working on the mode2 and I should be able to do that part myself from piecing together other code.

Keep up the great work and thanks again.

 

[AhoyNC]

You're welcome. Glad it worked for you. Thanks for the feedback.