Using values returned by formulas in other formulas

Philip Hanekom

Hi,

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

Etienne Mermillod

Wrap the call of the left with the value function such as :

=VALUE(LEFT(mycolumm4, 1))

This will convert the result into a number and allowing further operations.

Etienne Mermillod

Wrap the call of the left with the value function such as :

=VALUE(LEFT(mycolumm4, 1))

This will convert the result into a number and allowing further operations.

Philip Hanekom

So simple, thanks for helping out so quickly!

Shayila Bernard

Thank you I was able to convert my fields to values, but when I try to SUM those values in another field I keep getting 0?? How do I need to write my SUM formula when I select these converted fields?

Andrée Starå

Hi @Shayila Bernard,

Can you paste the formula you're using?

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Shayila Bernard

I figured it out.

My individual task had a formula to convert the value to a number.

=VALUE(LEFT(IF(Status@row = "Done", "100%", (IF(Status@row = "To Do", "0%", "")))))

Then I have a separate field to show me the % of those task that are completed.

=SUM([% Complete 1= 100%]3:[% Complete 1= 100%]22) / 20

I'm happy with the results. If there is a more efficient way to accomplish this calculation please let me know.

Thanks.

Andrée Starå

@Shayila Bernard

Excellent!

Glad you got it working!