Formula to find the average word used in a column
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
Best Answers
-
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 ")
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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.
-
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 ")
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P. that worked! Thank you so much!
-
No problem! I'm glad that worked for you.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@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:
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thanks so much @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?
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P. that worked, thank you!
-
@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):
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
If there is no word or string I want in the Title column, how can I return "N/A" instead of creating an average?
-
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. 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!