Problems using the MODE function

[MDANDY]

Hi guys,

Don't know if anyone can help with this, I think it's pretty simple, but can't find an example on Google that's similar enough!

I am looking to find the modal value when certain conditions apply.

I am using two sheets and wish to find the mode of a given production line (out of 3 possible 'lines', B, C & D). I have a list of 'products made' (Column A, Sheet 1), the 'runs' of these products (Column A, Sheet 2), where each product has multiple runs. Finally, the 'line' on which the product was ran (Column C, Sheet 2).

I wish to find the modal line on Sheet 1 for every product, based on the previous runs.

I've tried MODE, MATCH, IF & INDEX statements in a few different ways and think I may be over-complicating things!

Any help/advice would be appreciated, thanks!

[AliGW]

Welcome to the forum!

Will you please attach a sample Excel workbook? We won't be able to work with or manipulate a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[MDANDY]

Okay, so my data set is something like:

SHEET1:

CODE(A) DESCRIPTION(B) LINE(C)
AAA *** A
AAA *** A
AAA *** A
AAA *** B
AAB *** A
AAB *** B
AAB *** B
AAB *** B
AAC *** B
AAC *** C
AAC *** C
AAD *** B

etc


SHEET2:

CODE(A) DESCRIPTION(B) MODAL LINE(C)
AAA *** ?
AAB *** ?
AAC *** ?

So, on Sheet 2, I wish the CODE in Sheet 1 to be looked up, and if there is a match, for the mode then to be calculated.
I'm working with roughly 1000 codes and Data that constantly updates, with about 3000 runs of every product. Not sure if this is clear, but any help would be great.

[AliGW]

No, not a lot of help, sorry - see the instructions in post #2.

[MDANDY]

I've tried to attach an example Workbook, not sure if it's worked.

Doesn't really show any more than my post does

[AliGW]

No, and that's because you haven't read the instructions I gave you, specifically:

2. Make sure that your desired solution is also shown (mock up the results manually).

A series of question marks does not help us much.

Please help us to help you by providing all the details requested.

[MDANDY]

The question marks are where I would like my desired result (modal production line) to be returned. Sheet 2 contains more info

[MDANDY]

So, for example, the production lines (shown in Sheet 2, B/C/D) will have a modal line, AAB's is D in Sheet 2

[AliGW]

I'm going to try this one more time.

What are your expected outcomes in this table?

Excel 2016 (Windows) 32 bit

	A	B	C
1	TLA	Description	Modal Line
2	SBF	Petproject Grain Free Cat Chicken ^^	?
3	KAU	Green Dog Duck Turkey & Veg Adult Dog ^^	?
4	WSE	Wafcol Salmon & Potato Adult Senior ^^	?
5	HFE	HF24 Venison & Sweet Potato Dog WBDF	?
6	SGF	Grain Free Small Breed Chicken SwtPot & Herbs ^^	?
7	SBJ	Petproject Grain Free Cat Chicken With Turkey ^^	?
8	CLD	Power of Nature: Active Dog-Country Lamb Dog	?
9	HGC	HF24 Wild Boar Adult - Wild Boar with Potato ^^	?
10	CGF	Power of Nature: Low Carb Natural Cat-Fees Favourite	?
11	HFK	HF24 Duck & Potato Adult Dog WBWD ^^	?
12	WCH	Wolfit Complete WD Chicken & Brown Rice	?
13	SUH	Lovejoys Fish & Rice Adult Dog ^^	?
14	FKE	Bob & Lush Puppy 60% Duck & Potato ^^	?
15	MXE	Millies Wolfheart Working Dog 50% Lamb & Veg ^^	?
16	CFP	Natures Way & Wolfit Puppy Chicken With Brown Rice ^^	?

Sheet: Sheet1

Answers, please - the first five rows should be enough to see what you want. I know you know what you want, but we are not mind-readers.

[MDANDY]

My expected outcome would be the production line that is most frequently used (B/C/D), for every TLA (product), based on all 'runs' of every TLA. (MODAL LINE)

There are roughly 2000 TLA's and approximately 8000 product 'runs' (that I'm considering), so for every TLA (or product), there are multiple 'runs', sometimes on different 'lines'.

I'm guessing INDEX, MATCH & MODE will do it?

[AliGW]

Yes, but WHAT ARE THE ANSWERS that you are expecting in the first five lines? Or don't you know what they should be? Unless we know what we are aiming it, we can't help. It is not our job to manually work out the answer for you - it's your job to tell us the answer you want and then we will try to provide a formula.

So, please provide the answers you want to see in cells C2 to C6. You don't need to work out the whole dataset - just the first five.

Excel 2016 (Windows) 32 bit

	A	B	C
1	TLA	Description	Modal Line
2	SBF	Petproject Grain Free Cat Chicken ^^	ANSWER
3	KAU	Green Dog Duck Turkey & Veg Adult Dog ^^	ANSWER
4	WSE	Wafcol Salmon & Potato Adult Senior ^^	ANSWER
5	HFE	HF24 Venison & Sweet Potato Dog WBDF	ANSWER
6	SGF	Grain Free Small Breed Chicken SwtPot & Herbs ^^	ANSWER
7	SBJ	Petproject Grain Free Cat Chicken With Turkey ^^	?
8	CLD	Power of Nature: Active Dog-Country Lamb Dog	?
9	HGC	HF24 Wild Boar Adult - Wild Boar with Potato ^^	?
10	CGF	Power of Nature: Low Carb Natural Cat-Fees Favourite	?
11	HFK	HF24 Duck & Potato Adult Dog WBWD ^^	?
12	WCH	Wolfit Complete WD Chicken & Brown Rice	?
13	SUH	Lovejoys Fish & Rice Adult Dog ^^	?
14	FKE	Bob & Lush Puppy 60% Duck & Potato ^^	?
15	MXE	Millies Wolfheart Working Dog 50% Lamb & Veg ^^	?
16	CFP	Natures Way & Wolfit Puppy Chicken With Brown Rice ^^	?

Sheet: Sheet1

[Glenn Kennedy]

Try this array formula:
=INDEX($C$2:$C$86,MODE.SNGL(IF($A$2:$A$86=A2,MATCH($C$2:$C$86,$C$2:$C$86,0))))

It returns errors if there is only 1 matching value. If you want balnks instead, use:

=IFERROR(INDEX($C$2:$C$86,MODE.SNGL(IF($A$2:$A$86=A2,MATCH($C$2:$C$86,$C$2:$C$86,0)))),"")

OR if you want the only value, use this:

=iIFERROR(INDEX($C$2:$C$86,MODE.SNGL(IF($A$2:$A$86=A2,MATCH($C$2:$C$86,$C$2:$C$86,0)))),A2)

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[AliGW]

I'm glad you understand what is wanted, Glenn!!! punk.gif

[Glenn Kennedy]

We'll see. You seemed to be losing a little of your cool, so I thought I'd jump in wuith a guess...

[AliGW]

Yes, indeed I was. It was a bit like interviewing a politician ...