'Most Common' Formula

Travis Duehr

Hello,

I am trying to create metrics on tracking the versions of an applications we are developing, currently I am attempting to find which version is most successful after it has been run through a series of criteria.

Example:

Build 30 - Success

Build 31- Success

Build 31 - Failure

Build 32 - Success

The Application versions will continually be increasing as we roll out more changes and features, so pre-defined selections are not possible. Any ideas?

Paul Newcome

Ok. So on your source sheet, you are gong to want to add in a text/number column (can later be hidden) that contains the following on all rows:

=COUNTIFS([Build version]:[Build version], [Build version]@row, [Successful/Unsuccessful]:[Successful/Unsuccessful], "Successful")

Then on your metrics sheet you would use a formula such as

=JOIN(DISTINCT(COLLECT({Source Sheet Build version column}, {Source Sheet Helper column}, @cell = MAX({Source Sheet Helper column})), ", ")

Using the appropriate method for creating cross sheet references, this will pull together all Build Versions that have the most number of successes.

So if Build 1 had 30 and Build 2 had 29, then the formula on your metrics sheet would just show "Build 1", but if there were multiple builds tied for the most such as Build 1 has 30 and Build 2 has 30, it will show as "Build 1, Build 2"

Paul Newcome

Can you provide a mocked up screenshot of how you want the metrics to look as well as the layout of the source data?

Are the build number and the result in different columns? Are you wanting to just pull the build number that has the most amount of "Success" in the result column? What if there are multiple build numbers that have the same number of successes? Would you want the most recent/highest build number, the oldest/lowest build number, or a collection of all of the build numbers in one string?

Travis Duehr

This is how my layout is currently set up, with builds being stored in increasing fashion; each row is a flight, and continue to increase as new builds roll in and more pilots fly. Pilots will submit a series of criteria via a form which evaluates the success of the build. Essentially my data outputed for reporting this boils down to 2 columns, successful/unsuccessful and the build number. I then simply want to report on a portal view the build version with the most successful recorded flights

Paul Newcome

Ok. So on your source sheet, you are gong to want to add in a text/number column (can later be hidden) that contains the following on all rows:

=COUNTIFS([Build version]:[Build version], [Build version]@row, [Successful/Unsuccessful]:[Successful/Unsuccessful], "Successful")

Then on your metrics sheet you would use a formula such as

=JOIN(DISTINCT(COLLECT({Source Sheet Build version column}, {Source Sheet Helper column}, @cell = MAX({Source Sheet Helper column})), ", ")

Using the appropriate method for creating cross sheet references, this will pull together all Build Versions that have the most number of successes.

So if Build 1 had 30 and Build 2 had 29, then the formula on your metrics sheet would just show "Build 1", but if there were multiple builds tied for the most such as Build 1 has 30 and Build 2 has 30, it will show as "Build 1, Build 2"

KristynTucker22

Is there a way to calculate this with just one column? I have tried eliminating one part of the formula but am getting an "INVALID REF" error. I am trying to find the most common answer in a single column. For example, "Contact Preference" currently has 3 "Teams Chat", 1 "Teams Call", 1 "Weekly Status Meeting", and 1 "Email".

I'm sure I'm missing a small step, but these formulas can be tricky!

Paul Newcome

@KristynTucker22 That particular error means your cross sheet reference was not created properly.

Even after you update the cross sheet reference, you will still have a syntax issue where you will need to move one of the closing parenthesis from the very end to immediately after the MAX function.

MAX(.......))), ", ")

KristynTucker22

@Paul Newcome

Thanks for the info, however I think the item I may be missing is not having a reference sheet at all. Data is only pulling from the data referenced in the screen shots above.

Is there a way to re-work this without a cross sheet reference?

Paul Newcome

@KristynTucker22 Try replacing

@cell = MAX(.....)

with

@cell <> ""

(double quotes to say @cell is not blank)

KristynTucker22

@Paul Newcome That did it, Thank you so much!

Paul Newcome

@KristynTucker22 Happy to help. 👍️