Mode.Multi Function based on month

[seanpcorbett1]

I have a large list with varying prices of widgets within months... I'd like to find the most common value in each month... right now I'm having difficulty structuring the formula to grab only the prices for one particular month then applying the Mode.Sing or Mode.Multi function. Any help would be greatly appreciated!

Thanks,

Sean

 

[tusharm]

If your data are in a table with the months in a column named Mth and the values in a column named Val, to get a single mode use the array formula =MODE.SNGL(IF(Table1[Mth]=H2,Table1[Val])) where H2 contains the month of interest.

To enter an array formula complete data entry with CTRL+SHIFT+ENTER rather than just the ENTER or TAB key. If done correctly, *Excel* will show the formula within curly brackets { and }

If your data can have more than one mode, select enough cells to contain all the modes and enter the array formula =MODE.MULT(IF(Table1[Mth]=H2,Table1[Val]))

I have a large list with varying prices of widgets within months... I'd like to find the most common value in each month... right now I'm having difficulty structuring the formula to grab only the prices for one particular month then applying the Mode.Sing or Mode.Multi function. Any help would be greatly appreciated!

Thanks,

Sean