Using values returned by formulas in other formulas
I have a user populated field (using a dropdown) where the options range from “1-Innocence” to “5-Excellence” – this makes more sense to a user than simply having a 1-5 available for selection. To summarise these ratings per theme, focus area, assessment, etc. I obviously need to make use of the 1,2,3,4,5 values in the string. I extract that from the string using =left(), easy enough. But can then do no form of calculation, such as averaging, on the returned value.
I need to copy the result from the =left() function and paste it as a value before the =AVG() formula sees it for what it is. Obviously I want to set up the formulas and summaries to refer to the returned values and not have to manually do something every time someone makes a change.
Hope I'm making a mistake and that there's an easy fix. Thanks
Help Article Resources
Check out the Formula Handbook template!