MODE + Multiple Sheets + Excluding 0 and NULL or else Finding Second Most Common

**xjansand** · 12-16-2014, 06:16 PM

Hi all,

I have a worksheet where I track the prices of many items from different stores. It's a huge workbook, so I have a distinct tab for each store.

Each tab lists each item's SKU, description, many other things, and eventually the price. There is only a price if that store carries that item, so a lot of the cells in these columns are blank.

I'm trying to make something that returns the most common price for a particular item, excluding "0" and NULL as possible results.

Here's what I've already tried:

1) =MODE('FirstTab:LastTab'!A1) doesn't work.

2) I followed this advice: http://answers.microsoft.com/en-us/o...b-c4cc86351764

...Which seems to always returns $0.00. I never put "$0.00" in as a value for my product price, so I figure that the formula must be interpreting all null values as $0.

3) I created a formula with IF that says "If the mode is greater than zero, display the mode. Otherwise, say NONE." This is what I came up with: =IF(GetMode("FirstTab","LastTab",A1)>0,GetMode("FirstTab","LastTab",A1),"NONE")

This is only returning "NONE" - I realized it's because $0 (which is actually a null cell in each tab) will always be the most common value.

So, now I'm trying to either find the second most common value across worksheets, or else exclude "NULL" and "0" when calculating MODE.

Any ideas? Maybe a second most common formula to replace the "NONE" in 3)'s formula?

Thanks in advance for the help!

**Glenn Kennedy** · 12-17-2014, 05:24 AM

So far, 39 views and no replies... It would be easier if we could see what you'rew doing. So.....

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**xjansand** · 12-17-2014, 02:16 PM

Okay, no problem.

I've cleaned out most of the rows and columns to make it easier to understand.

The Master Sheet has a "Desired Mode" column stating what I'd like to have returned.

Is that helpful?

This is a work spreadsheet that we've been using for years. I've been trying to make it more useful.

**XOR LX** · 12-17-2014, 02:28 PM

Hi.

So can you just clarify which columns are of interest in this workbook?

Regards

**xjansand** · 12-17-2014, 02:41 PM

I'm trying to make the Master Sheet's R Column Find the MODE of the other sheets' Q Columns.

Master Sheet's S shows what result I'd like to generate in the R column for each row.

Basically: return the MODE. If the MODE is "0" (NULL, really), return the second most common value. If there is no value listed for a particular item on any of the store tabs, return "NONE". If there is a tie for second most common, either return "NONE" or all of the tied values (I'm not picky on this one).

**XOR LX** · 12-17-2014, 03:00 PM

Thanks. What if more than one entry shares the modal value? You talk about what you would like to happen if this is the case for the second most common, though not the first.

Also, if you want to return "all of the tied values", is this to be in separate cells, e.g. R2, S2, T2, etc. (however many it takes), or into the same cell, e.g. comma-separated?

If the latter, then, unless you can guarantee that there'll never be more than, say, two entries tying for the modal value for any given product, a formula-based solution will quickly become unfeasible and you'd be best off re-posting in the VBA section of this forum.

Returning multiple entries to separate cells using worksheet formulas alone is straightforward: concatenating multiple entries into a single cell using worksheet formulas alone is, beyond a certain number of returns, practically impossible.

Regards

**xjansand** · 12-17-2014, 04:28 PM

Thanks - I might repost to VBA too.

I definitely prefer comma separated, or else just "NONE". I want to know, basically, if there's a price that is clearly used most.

**XOR LX** · 12-17-2014, 05:04 PM

Bit confused. Like I said, comma-separated would be unfeasible using formulas alone, though "None" would not.

Perhaps you need to make a clear decision on this, given that the two options require such different approaches?

Regards

**xjansand** · 12-17-2014, 05:09 PM

Then I chose "NONE" because I'm less concerned about what shows up if there's a tie, and more concerned about the rest. The most important thing is to know whether one particular value is a clear winner. If one is not a clear winner, I'm not particular about what is returned.

So maybe I could restate this...

Is there a way to use this "GetMode" formula, in conjunction with something that ignores null values?

**XOR LX** · 12-18-2014, 03:54 AM

Thanks.

Your desired result for Product G is "NONE", yet that product has two non-zero entries of $7.51 (StoreD and StoreE).

Can you clarify?

Regards

**xjansand** · 12-18-2014, 02:47 PM

You're right - the desired result should be 7.51.

Here's an updated version.

**XOR LX** · 12-19-2014, 05:22 AM

Not straightforward, I'm afraid, but this array formula** in R2 should work:

=IFERROR(IF(COUNT(MODE.MULT(IFERROR(1/(1/(IF(T(OFFSET(INDIRECT("'"&Sheets&"'!A2"),ROW(INDIRECT("1:20"))-1,,,))=A2,N(OFFSET(INDIRECT("'"&Sheets&"'!Q2"),ROW(INDIRECT("1:20"))-1,,,))))),""),IFERROR(1/(1/(IF(T(OFFSET(INDIRECT("'"&Sheets&"'!A2"),ROW(INDIRECT("1:20"))-1,,,))=A2,N(OFFSET(INDIRECT("'"&Sheets&"'!Q2"),ROW(INDIRECT("1:20"))-1,,,))))),"")))>1,"NONE",MODE.MULT(IFERROR(1/(1/(IF(T(OFFSET(INDIRECT("'"&Sheets&"'!A2"),ROW(INDIRECT("1:20"))-1,,,))=A2,N(OFFSET(INDIRECT("'"&Sheets&"'!Q2"),ROW(INDIRECT("1:20"))-1,,,))))),""),IFERROR(1/(1/(IF(T(OFFSET(INDIRECT("'"&Sheets&"'!A2"),ROW(INDIRECT("1:20"))-1,,,))=A2,N(OFFSET(INDIRECT("'"&Sheets&"'!Q2"),ROW(INDIRECT("1:20"))-1,,,))))),""))),"NONE")

Note that the 20 in the portion:

INDIRECT("1:20")

determines the number of rows over which the formula is calculated, which must be at least equal to the maximum number of populated rows from all worksheets concerned (in your sample workbook this values is 17, so 20 was sufficient). Note that this number should not be made too large, which will cause unnecessary additional calculation of an already resource-heavy formula set-up.

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

MODE + Multiple Sheets + Excluding 0 and NULL or else Finding Second Most Common

LinkBack

Thread Tools

Rate This Thread

Display

MODE + Multiple Sheets + Excluding 0 and NULL or else Finding Second Most Common

Re: MODE + Multiple Sheets + Excluding 0 and NULL or else Finding Second Most Common

Re: MODE + Multiple Sheets + Excluding 0 and NULL or else Finding Second Most Common

Re: MODE + Multiple Sheets + Excluding 0 and NULL or else Finding Second Most Common

Re: MODE + Multiple Sheets + Excluding 0 and NULL or else Finding Second Most Common

Re: MODE + Multiple Sheets + Excluding 0 and NULL or else Finding Second Most Common

Re: MODE + Multiple Sheets + Excluding 0 and NULL or else Finding Second Most Common

Re: MODE + Multiple Sheets + Excluding 0 and NULL or else Finding Second Most Common

Re: MODE + Multiple Sheets + Excluding 0 and NULL or else Finding Second Most Common

Re: MODE + Multiple Sheets + Excluding 0 and NULL or else Finding Second Most Common

Re: MODE + Multiple Sheets + Excluding 0 and NULL or else Finding Second Most Common

Re: MODE + Multiple Sheets + Excluding 0 and NULL or else Finding Second Most Common

Thread Information

Users Browsing this Thread

Similar Threads

Mode? Help with finding most common value

Copy specific cell from multiple sheets to summary - excluding some sheets

Excel 2007 : Finding output across w/sheets by common data

Finding common Data in multiple worksheets

[SOLVED] Finding common data in multiple columns and rows in Excel

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions