Convert Measure Symbol to number & Average

MeganJF

Hi,

Since my team has selected the pain scale symbols as their preferred method of scoring work, here I am struggling to make the following formula work.

=IF([Value to User]@row = "No Pain", 4, IF([Value to User]@row = "Mild", 3, IF([Value to User]@row = "Moderate", 2, IF([Value to User]@row = "Very Severe", 1, IF([Value to User]@row = "Extreme", 0)))))

The team can still select the appropriate smiley (or not so smiley) face and view it in the cell, but I would like to be converting each to a number on the sheet summary and my metrics sheet so that I can get a collective average to report out on.

At the row level, I will be averaging across all four (4) criteria - This is what I have tried there

=AVG(IF([Value to User]@row = "No Pain", 4, IF([Value to User]@row = "Mild", 3, IF([Value to User]@row = "Moderate", 2, IF([Value to User]@row = "Very Severe", 1, IF([Value to User]@row = "Extreme", 0))))) + =IF([Criteria2]@row = "No Pain", 4, IF([Criteria2]@row = "Mild", 3, IF([Criteria2]@row = "Moderate", 2, ….)))))+ =IF([Criteria 3]@row = "No Pain", 4,…..))))) + =IF([Criteria 4]@row = "No Pain", 4,…..))))))

I want to find an average for each column.

I am getting the response "UNPARSEABLE"

Jason Albrecht

Hi @MeganJF,

Thanks for a challenging question. Following on from @Kelly Moore 's excellent reply, you may be able to simplify the equation further, if the columns [Value to User], [Criteria2], etc, are side by side, or there are no other columns between these that have the same wording you are looking for. So, by using CountIFS with AVG, the formula could look something like:

=AVG(COUNTIFS([Value to User]:[Criteria 3], "No Pain") * 4, COUNTIFS([Value to User]:[Criteria 3], "Mild") * 3, COUNTIFS([Value to User]:[Criteria 3], "Moderate") * 2, COUNTIFS([Value to User]:[Criteria 3], "Very Severe") * 1, COUNTIFS([Value to User]:[Criteria 3], "Extreme") * 0)

By the way: I'm not sure if you are meaning to take "Extreme" out of the average equation, but by assigning "Extreme" a Zero value, it is not counted in the Average equation.

Let us know how you go.

Kelly Moore

@MeganJF

Try this

=AVG(IF([Value to User]@row = "No Pain", 4, IF([Value to User]@row = "Mild", 3, IF([Value to User]@row = "Moderate", 2, IF([Value to User]@row = "Very Severe", 1, IF([Value to User]@row = "Extreme", 0))))), IF([Criteria2]@row = "No Pain", 4, IF([Criteria2]@row = "Mild", 3, IF([Criteria2]@row = "Moderate", 2, IF([Criteria2]@row = "Very Severe", 1)))), IF([Criteria 3]@row = "No Pain", 4, IF([Criteria 3]@row = "Mild", 3, IF([Criteria 3]@row = "Moderate", 2, IF([Criteria 3]@row = "Very Severe", 1, IF([Criteria 3]@row = "Extreme", 0))))), IF([Criteria 4]@row = "No Pain", 4, IF([Criteria 4]@row = "Mild", 3, IF([Criteria 4]@row = "Moderate", 2, IF([Criteria 4]@row = "Very Severe", 1, IF([Criteria 4]@row = "Extreme", 0))))))

Will this work for you

Kelly

Jason Albrecht

Hi @MeganJF,

Thanks for a challenging question. Following on from @Kelly Moore 's excellent reply, you may be able to simplify the equation further, if the columns [Value to User], [Criteria2], etc, are side by side, or there are no other columns between these that have the same wording you are looking for. So, by using CountIFS with AVG, the formula could look something like:

=AVG(COUNTIFS([Value to User]:[Criteria 3], "No Pain") * 4, COUNTIFS([Value to User]:[Criteria 3], "Mild") * 3, COUNTIFS([Value to User]:[Criteria 3], "Moderate") * 2, COUNTIFS([Value to User]:[Criteria 3], "Very Severe") * 1, COUNTIFS([Value to User]:[Criteria 3], "Extreme") * 0)

By the way: I'm not sure if you are meaning to take "Extreme" out of the average equation, but by assigning "Extreme" a Zero value, it is not counted in the Average equation.

Let us know how you go.

MeganJF

MeganJF

My comment above is not showing - Repeating what I said before.

Still getting UNPARSEABLE as a response.

For the single column average, I am using Jason's suggestion:

=AVG(COUNTIFS([Value to User]:[Value to User], "No Pain") * 5, COUNTIFS([Value to User]:[Value to User ], "Mild") * 4, COUNTIFS([Value to User]:[Value to User ], "Moderate") * 3, COUNTIFS([Value to User]:[Value to User ], "Very Severe") * 2, COUNTIFS([Value to User]:[Value to User ], "Extreme") * 1)

*Also tried [Value to User]@row

For the average across all four criteria columns, I am using:

=AVG(IF([Value to User]@row = "No Pain", 5, IF([Value to User]@row = "Mild", 4, IF([Value to User]@row = "Moderate", 3, IF([Value to User]@row = "Very Severe", 2, IF([Value to User]@row = "Extreme", 1))))), IF([Level of Engagement]@row = "No Pain", 5, IF([Level of Engagement]@row = "Mild", 4, IF([Level of Engagement]@row = "Moderate", 3, IF([Level of Engagement]@row = "Very Severe", 2, IF([Level of Engagement] = "Extreme", 1)))), IF([Research Informed]@row = "No Pain", 5, IF([Research Informed]@row = "Mild", 4, IF([Research Informed]@row = "Moderate", 3, IF([Research Informed]@row = "Very Severe", 2, IF([Research Informed]@row = "Extreme", 1))))), IF([Design Delivered vs Developed]@row = "No Pain", 5, IF([Design Delivered vs Developed]@row = "Mild", 4, IF([Design Delivered vs Developed]@row = "Moderate", 3, IF([Design Delivered vs Developed]@row = "Very Severe", 2, IF([Design Delivered vs Developed]@row = "Extreme", 1))))))

MeganJF

UPDATE!! I GOT IT!

It's always such a happy moment when you see all the content in the formula light up with different colors.

For the single column average:

=AVG(COUNTIFS([Value to User]:[Value to User], "No Pain") * 5, COUNTIFS([Value to User]:[Value to User], "Mild") * 4, COUNTIFS([Value to User]:[Value to User], "Moderate") * 3, COUNTIFS([Value to User]:[Value to User], "Very Severe") * 2, COUNTIFS([Value to User]:[Value to User], "Extreme") * 1)

For the across all columns average:

=AVG(COUNTIFS([Value to User]@row:[Design Delivered vs Developed]@row, "No Pain") * 5, COUNTIFS([Value to User]@row:[Design Delivered vs Developed]@row, "Mild") * 4, COUNTIFS([Value to User]@row:[Design Delivered vs Developed]@row, "Moderate") * 3, COUNTIFS([Value to User]@row:[Design Delivered vs Developed]@row, "Very Severe") * 2, COUNTIFS([Value to User]@row:[Design Delivered vs Developed]@row, "Extreme") * 1)

Jason Albrecht

That's great news!

Thank you for the update, and yes, an absolute delight to see the colours confirming the equation is working.

As an FYI, I noticed in your first reply there were some spaces between the column name and the square bracket (e.g. [Value to User]:[Value to User ]) which may have resulted in the UNPARSEABLE error.

For clarification, I was able to confirm the equation worked without needing the @row reference.

Regardless; it's great to hear you found a way to make it work. Congrats.