Creating an Array formula

[induna]

On a machine that provides measurements at 4 data points, over any range of intervals, say hourly or daily, what are the most common 4 data points in that period.
In my example spreadsheet by visual observation, the result is shown as the most common data points.In this example of data points over 12 days I can see visually and
calculate the most common data points were 1 -13-11-10
How do I create a formula for four machine data points?
And a formula for a 2nd machine with 6 data points?
I'm not sure how to create the array formula to calculate this.

 

[proficient]

Welcome to the board,

Do you have office 365?

 

[Fluff]

Do you have office 365?

The OP's profile clearly shows 2016 so the answer is NO

 

[induna]

Thank you, I will try this for both the 4 and 6 datapoint systems. I will test and let you know.

 

[Fluff]

If you are using xl2016 as per your profile, then you do not have those functions.

 

[induna]

Hi Kamran,
Sorry, it did not work for me.

 

[induna]

If you are using xl2016 as per your profile, then you do not have those functions.

Thank you Fluff, is there a function that could produce the result I'm looking for?

 

[Fluff]

Can you post some sample dat, rather than an image.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[proficient]

Please confirm this with CTRL + SHIFT + ENTER

This is the best I could do, and you have to use TEXT TO COLUMN with the space delimiter to separate them.

The formula for your ease. just copy and paste into your file and adjust the range accordingly (if needed).

=TRIM(CONCAT(IF(COUNTIF(A1:D11,ROW(1:99))=MAX(COUNTIF(A1:D11,ROW(1:99))),ROW(1:99),"")&" "))

 

[Fluff]

Unfortunately Excel 2016 doesn't have the CONCAT function.