Formula to find the average word used in a column

Ann11

Hi,

Is there a formula I can use to find the average "word" used in a column?

For example, I want to find the average of these words from the drop-down list:

Cheers!

Ann

Genevieve P.

Hi @Ann11

Thank you for clarifying! In this case you only need the COUNTIF formulas, since this will immediately tell you how many times a value appears in a column.

You could also create a Report to quickly Group and Summarize this sheet based on your Rating value, to easily see the number of each Rating and even use it in a Chart widget (such as a Pie Chart) if that would be helpful.

See: Redesigned Reports with Grouping and Summary Functions

However, if you would prefer to return the text that has the greatest Count instead of just finding the numbers, then you could use the IF statement to check to see which Count is the greatest.

For example, this first statement says, if the COUNT of all the cells that say "High" is greater than both Medium and Low cell counts, then return "High".

=IF(AND(COUNTIF(Rating:Rating, "High") >= COUNTIF(Rating:Rating, "Medium"), COUNTIF(Rating:Rating, "High") >= COUNTIF(Rating:Rating, "Low")), "High")

Then we can add the other two statements to this, to return either Medium or Low if they have the greatest number of cells:

IF(AND(COUNTIF(Rating:Rating, "High") >= COUNTIF(Rating:Rating, "Medium"), COUNTIF(Rating:Rating, "High") >= COUNTIF(Rating:Rating, "Low")), "High ") + IF(AND(COUNTIF(Rating:Rating, "Medium") >= COUNTIF(Rating:Rating, "High"), COUNTIF(Rating:Rating, "Medium") >= COUNTIF(Rating:Rating, "Low")), "Medium ") + IF(AND(COUNTIF(Rating:Rating, "Low") >= COUNTIF(Rating:Rating, "Medium"), COUNTIF(Rating:Rating, "Low") >= COUNTIF(Rating:Rating, "High")), "Low ")

Genevieve P.

Hi @Ann11

Yes! Anywhere we have a COUNTIF statement, you can change this to be a COUNTIFS, plural, then list your second column and second criteria:

COUNTIFS(Rating:Rating, "High, [Column2]:[Column2], "Open")

Let me know if you need help seeing the full formula, or if that's enough to help adjust the statements.

It may be easier to go down the Report route: create a Report, filter by "Open", then use the Grouping and Summary function to immediately gather numbers for your different Ratings. Then you could use this as a Pie Chart in a Dashboard so it's quick and easy to see which one has the most.

Cheers,

Genevieve

Genevieve P.

Hi @Ann11

The way I would do this is to set up three Sheet Summary Fields (if your plan has access to Sheet Summaries). Then you can use a COUNTIF Function to count how many times each of your words appears, like so:

=COUNTIF(Rating:Rating, "High")

=COUNTIF(Rating:Rating, "Medium")

=COUNTIF(Rating:Rating, "Low")

Once you have the three values, you can then decide how you want to surface the "Average".

Are you looking for the one value that has more than either of the others?

Or do you want to assign a "weight" to each of the text outputs, then depending on what the total number is for that column, find the average of the column and assign a range for each word...

ex:

Low = 1

Medium = 1.5

High = 2

You can SUM together those numbers and divide it by the number of rows:

=SUM([Summary High]# * 2, [Summary Medium]# * 1.5, [Summary Low]#) / COUNT([Primary Column]:[Primary Column])

This will give you an average number.

Then you can say, "if the number is below 1.25, it's Low, if it's below 1.75 it's Medium, otherwise it's High" or something like that.

=IF(Average# < 1.25, "Low", IF(Average# < 1.75, "Medium", "High"))

You could of course write this all in one formula without Summary Fields, but I find it's easier to break it out into bite-sized pieces. Here is an example of a nested formula that could create this calculation for you:

=IF(SUM(COUNTIF(Rating:Rating, "High") * 2, COUNTIF(Rating:Rating, "Medium") * 1.5, COUNTIF(Rating:Rating, "Low")) / COUNT([Primary Column]:[Primary Column]) < 1.25, "Low", IF(SUM(COUNTIF(Rating:Rating, "High") * 2, COUNTIF(Rating:Rating, "Medium") * 1.5, COUNTIF(Rating:Rating, "Low")) / COUNT([Primary Column]:[Primary Column]) < 1.75, "Medium", "High"))

Cheers,

Genevieve

Ann11

Hi @Genevieve P.

Sorry I should have worded the question better. I want to find the MODE of the rating but I realised that SmartSheet doesn't have a MODE function for formulas. Is there a formula I can use that is like the MODE formula in excel? E.g based on the screenshot provided, the value that it will return is "low" since low appears the most in that column.

Genevieve P.

Hi @Ann11

Thank you for clarifying! In this case you only need the COUNTIF formulas, since this will immediately tell you how many times a value appears in a column.

You could also create a Report to quickly Group and Summarize this sheet based on your Rating value, to easily see the number of each Rating and even use it in a Chart widget (such as a Pie Chart) if that would be helpful.

See: Redesigned Reports with Grouping and Summary Functions

However, if you would prefer to return the text that has the greatest Count instead of just finding the numbers, then you could use the IF statement to check to see which Count is the greatest.

For example, this first statement says, if the COUNT of all the cells that say "High" is greater than both Medium and Low cell counts, then return "High".

=IF(AND(COUNTIF(Rating:Rating, "High") >= COUNTIF(Rating:Rating, "Medium"), COUNTIF(Rating:Rating, "High") >= COUNTIF(Rating:Rating, "Low")), "High")

Then we can add the other two statements to this, to return either Medium or Low if they have the greatest number of cells:

IF(AND(COUNTIF(Rating:Rating, "High") >= COUNTIF(Rating:Rating, "Medium"), COUNTIF(Rating:Rating, "High") >= COUNTIF(Rating:Rating, "Low")), "High ") + IF(AND(COUNTIF(Rating:Rating, "Medium") >= COUNTIF(Rating:Rating, "High"), COUNTIF(Rating:Rating, "Medium") >= COUNTIF(Rating:Rating, "Low")), "Medium ") + IF(AND(COUNTIF(Rating:Rating, "Low") >= COUNTIF(Rating:Rating, "Medium"), COUNTIF(Rating:Rating, "Low") >= COUNTIF(Rating:Rating, "High")), "Low ")

Ann11

@Genevieve P. that worked! Thank you so much!

Genevieve P.

No problem! I'm glad that worked for you.

Ann11

@Genevieve P. just wondering if there a way I can use this formula:

IF(AND(COUNTIF(Rating:Rating, "High") >= COUNTIF(Rating:Rating, "Medium"), COUNTIF(Rating:Rating, "High") >= COUNTIF(Rating:Rating, "Low")), "High ") + IF(AND(COUNTIF(Rating:Rating, "Medium") >= COUNTIF(Rating:Rating, "High"), COUNTIF(Rating:Rating, "Medium") >= COUNTIF(Rating:Rating, "Low")), "Medium ") + IF(AND(COUNTIF(Rating:Rating, "Low") >= COUNTIF(Rating:Rating, "Medium"), COUNTIF(Rating:Rating, "Low") >= COUNTIF(Rating:Rating, "High")), "Low ")

Along with a formula that would only count these words if the status was "open"?

For example:

Genevieve P.

Hi @Ann11

Yes! Anywhere we have a COUNTIF statement, you can change this to be a COUNTIFS, plural, then list your second column and second criteria:

COUNTIFS(Rating:Rating, "High, [Column2]:[Column2], "Open")

Let me know if you need help seeing the full formula, or if that's enough to help adjust the statements.

It may be easier to go down the Report route: create a Report, filter by "Open", then use the Grouping and Summary function to immediately gather numbers for your different Ratings. Then you could use this as a Pie Chart in a Dashboard so it's quick and easy to see which one has the most.

Cheers,

Genevieve

Ann11

Thanks so much @Genevieve P.!

Ann11

Hi @Genevieve P.

I tried using this formula for this sheet and the result came out blank (no value and no error).

This was the sheet:

This was the formula I used:

Do you know what I did wrong?

Genevieve P.

Hi @Ann11

A blank return means that one of your IF statements is false, so instead of outputting "text" it returns blank.

If your third IF statement, after the output of "High - 3", I see you're searching for

IF(AND(COUNTIF(Rating:Rating, "Medium")

Should this be:

IF(AND(COUNTIFS(Rating:Rating, "Medium-2", Status:Status, "Open") ?

If this wasn't the issue, try each IF statement separately to see what they return.

Cheers!

Genevieve

Ann11

@Genevieve P. that worked, thank you!

Ann11

@Genevieve P. is there a way I can generate multiple averages? For example I want to find the average result of the different names under the Title column (the first column):

Genevieve P.

Hi @Ann11

The fastest way to do this would be to create a Report, GROUP by this column, then use SUMMARIZE to COUNT how many times each number appears.

Then you can add this as a Pie Chart, so you can see the percent of each value typed.

Otherwise, you can use the same formula above but change what column it's looking at and what values it's searching for.

Cheers,

Genevieve

Ann11

Hi @Genevieve P.

If there is no word or string I want in the Title column, how can I return "N/A" instead of creating an average?

Genevieve P.

Hi @Ann11

In this instance I would recommend adjusting the formula you have in the Rating column so it doesn't show a rating for that value, or returns "N/A" instead.

Or, if you're using a Report, you can filter out those titles. 🙂