Excel Magic Trick 1304: List Text Modes in Single Cell TEXTJOIN, INDEX, N, IF, MATCH & MODE.MULT

Download Excel Files:

Learn how to create an array formula to show a sorted list of modes in a single cell. Learn how to get INDEX function to deliver an array of items using the formula element: N(IF(1,Array)). Thanks to Bill Szysz and XLarium at YouTube and to this amazing blog:

[MalinaC]

It's absolutely brilliant! I wolud never think of putting N and IF into this formula. It works perfectly, thank you!

[jasonm]

I'm all for Excel Magic, but I fear this may be entering into the realm of Excel mysticism. :)

[justinacciavatti]

Thanks for posting Mike - I used the N(if( setup here to do a conditional sum of an index and it works with something like this: ={SUM(INDEX($C$3:$H$8, N(IF($B$3:$B$7>3, B3:B7, 6)), N(IF(C2:G2>4, C2:G2, 6))))} , but is a little wonky because if I don't put the If(false, range +1) in there (the "6" when each array is 5 cells), the formula returns 0 inside the index and gives a value; also, if nothing meets the criteria, it gives a REF# error rather than a you can improve upon it - I like the concept because I often want to sum within an index and use a different sort of array setup

[edwardwatson]

Hi Mike, I got a stumper for you. I've embedded an Excel 2010 table into PowerPoint 2010, with four pivot tables and four slicers. Each slicer is linked to all four pivot tables. The source Excel file I saved as an Access database (which I linked to the PP file).When I do this in Excel, it's easy-peasy and I don't run into any problems, but embedding the pivot tables into PP so that the managers can see different data results using the slicers during the presentation (I'm making an interactive PP) creates errors--but oddly NOT with the pivot tables, but with a table that summarizes the totals of the four pivot tables.You see, I created a summary table of the totals of the four pivot tables for instant comparisons (i.e., budget, cash flow, actual, etc.). When I did this in Excel, the cell data says "=GETPIVOTDATA ..." and highlights the top left row of the referenced pivot table despite I did a simple "=##" (where "##" is the pivot table cell that I was copying).Absolutely no problems in Excel. However, when I do the exact SAME thing in an embedded Excel sheet within PowerPoint, the cell data does NOT say "=GETPIVOTDATA ..." but just does an "=##" (e.g., "=B42").The practical result of this difference is when one of the pivot tables shifts size due to different slicer conditions, the summary table just shows "$0.00" in every cell because what was originally "B42" is now B41 or B40. (For example, in all four pivot tables, the row labels are based on a risk ranking, but some project portfolio areas and project managers [both of which are separate slicers] do not have all risk types [for example, some may not have any projects with a risk rank of "IV"]. I've tried pasting links, using every permutation of F4, formulas, and everything else I can think of, but no matter what I do, that summary table will not stick to the relevant pivot table cell when the Excel file is embedded in PowerPoint.Help!Ed:-)

[nadermounir]

Amazing video 📹 N function is very interesting 👌

[sanjayprajapat]

is there any Trick which can be used to-" Gather data from various multi column sheets to same format in one another excel same format file." Data needs to come below the one another like sheet1 data, than below sheet 2 data, than below sheet 3 data, I have almost 30-40 sheets like that." can you share some trick. I will be obliged.

[Sal_A]

What do you mean from 3:13  - 3:22? Regarding N in dereferencing arrays in Subtotal function?

[katjushenkahahn]

Hello. I need somebody's help. I need to set ranks of some number column depending on south west etc. that in other text column. Directions are not in order. I wanted to use match function to find directinon. Use it as range to find rank in of given value in number column. But match function finds only first and doesn't work as array formula. Please help me!!! Thank you.

[excel]

Hey Mike i have a excel problem which i would like to solve via excel Tools and not per vba. Is there any possibility to add 1 to a Single cell when ever i hit F9 to recalculate the spreedsheet? I know excel doesnt like circular dependencies but maybe you have an Idee how so solve this little but tricky problem?

[RambozoClown]

I guess that makes this a real Excel "magic" trick

[josdiepenbeek]

Seems a useful function to me, that "N". Gonna keep that in mind. ;-)

[chandrajitshah]

Hi,
I have a very simple question according to that i am not able to set a proper formula for that can anyone help me to solve my issue.. please mention your email id so that i can able to forward the data of excel in which i am facing issue.. Help help


Thanks and Regards,
Chandrajit Kumar Shah