Which formulae is it to compare 3 values and give me 1 of 3 different text options in a cell?

Rob Barker

I have created a form that populates a sheet and the form asks multiple questions and then the sheet uses the answers to create 3 costs, so far very simple. However I want another cell to automatically display a result based on the 3 costs provided. I'm a contract manager and I will have 3 options I wish to display they are:

*Life Expectancy Comprehensive Contract Cost

*Life Expectancy In- House maintenance Comprehensive Cost

*Life Expectancy Partnership maintenance Cost

I want my cell "Matrix Outcome" to display the one of three above based on the cheapest/lowest cost option. Is this a formulae, Automation or Conditional format. Please explain how to do this if possible picture below for example. The Matrix Outcome should display "Life Expectancy Comprehensive Contract Cost"

Thanks Rob

Andrée Starå

@Rob Barker

Glad we got it working!

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

Andrée Starå

Hi @Rob Barker

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@workbold.com)

I hope that helps!

Be safe and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

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

Andrée Starå

@Rob Barker

Here's the formula that hopefully worked.

=MIN(COLLECT([Life Expectancy Comprehensive Contract Cost]@row:
[Life Expectancy Partnership Maintenance Cost]@row, 
[Life Expectancy Comprehensive Contract Cost]@row:
[Life Expectancy Partnership Maintenance Cost]@row, <>""))

Did it?

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

Rob Barker

Hi @Andrée Starå ,

I tried the formulae and made some slight adjustments by changing the rows to 1 as it will calculate it per row so I will make it a column formulae. Unfortunately it returned a figure of 4500.

Any further advice would be appreciated.

Thanks for your help thus far Rob

Andrée Starå

@Rob Barker

Happy to help!

If you want it to be a column formula, it has to be @row at the end.

Can you paste the exact formula or share the sheet?

Andrée Starå

@Rob Barker

Glad we got it working!

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

Rob Barker

@Andrée Starå what a star thanks very much really appreciate your help with this. Thanks again Rob