Need help with nested functions

[ten4info]

I have multiple options from a field that are then being converted into a number value. I need there to be a sequential order of starting with one cell and executing an if statement. However, if that first one is not true, then it moves on to the next cell which would have multiple options (of the opportunity stage) that will return a number value for the stage of the opportunity.

To simplify:

If yes in the first cell, moves on to the next cell to determine the stage value of the opportunity. If no in the first cell, then the second level of inquiry is disregarded and a stage value is returned.

[Steve]

What do you mean by “cell”?

What does “moves on to the next cell” mean?

See also:

help.appsheet.com

Conditional Expressions

How to use expressions so that behavior depends on a set of conditions.

[ten4info]

I may have figured it out by just marrying up the sequences that would most likely be possible. I started with on expression that worked and then trial/error the functions that I needed to insert to make it work with the next option and then on down the line. I’m not sure if there is an easier or more concise way of formatting the formulation, but this is what it looks like:

=if(OR(AQ43=“Research review”),"-1",if(OR(BA43=“No”),"-99",if(AND(BA43="",AQ43=“Initial research provided”),“0”,if(AND(BA43="",AQ43=“Identity confirmed”),“1”,if(AND(BA43="",AQ43=“Telephone and/or email contact made”),“2”,if(AND(BA43="",AQ43=“Personal meeting/presentation”),“3”,if(AND(BA43="",AQ43=“Verbal acknowledgement”),“4”,if(AND(BA43="",AQ43=“Executed agreement”),“5”,if(AND(BA43="",AQ43=“On market”),“6”,if(AND(BA43="",AQ43=“Under contract”),“7”,if(AND(BA43="",AQ43=“Closed”),“8”,if(AND(BA43="",AQ43=“Active repeat client”),“9”,if(AND(BA43="",AQ43=“Multi-transaction client”),“10”,if(AND(BA43="",AQ43=“Research review”),"-1",if(AND(BA43=“Yes”,AQ43=“Initial research provided”),“0”,if(AND(BA43=“Yes”,AQ43=“Identity confirmed”),“1”,if(AND(BA43=“Yes”,AQ43=“Telephone and/or email contact made”),“2”,if(AND(BA43=“Yes”,AQ43=“Personal meeting/presentation”),“3”,if(AND(BA43=“Yes”,AQ43=“Verbal acknowledgement”),“4”,if(AND(BA43=“Yes”,AQ43=“Executed agreement”),“5”,if(AND(BA43=“Yes”,AQ43=“On market”),“6”,if(AND(BA43=“Yes”,AQ43=“Under contract”),“7”,if(AND(BA43=“Yes”,AQ43=“Closed”),“8”,if(AND(BA43=“Yes”,AQ43=“Active repeat client”),“9”,if(AND(BA43=“Yes”,AQ43=“Multi-transaction client”),“10”,if(AND(BA43=“Yes”,AQ43=“Research review”),"-1",if(AND(BA43=“Unknown”,AQ43=“Initial research provided”),“0”,if(AND(BA43=“Unknown”,AQ43=“Identity confirmed”),“1”,if(AND(BA43=“Unknown”,AQ43=“Telephone and/or email contact made”),“2”,if(AND(BA43=“Unknown”,AQ43=“Personal meeting/presentation”),“3”,if(AND(BA43=“Unknown”,AQ43=“Verbal acknowledgement”),“4”,if(AND(BA43=“Unknown”,AQ43=“Executed agreement”),“5”,if(AND(BA43=“Unknown”,AQ43=“On market”),“6”,if(AND(BA43=“Unknown”,AQ43=“Under contract”),“7”,if(AND(BA43=“Unknown”,AQ43=“Closed”),“8”,if(AND(BA43=“Unknown”,AQ43=“Active repeat client”),“9”,if(AND(BA43=“Unknown”,AQ43=“Multi-transaction client”),“10”,if(AND(BA43=“Unknown”,AQ43=“Research review”),"-1"))))))))))))))))))))))))))))))))))))))

[Steve]

Ah, you need a spreadsheet formula. I don’t help with those.

[ten4info]

Yeah, since there is obvious overlap and synergies, I was thinking that someone could easily share the solution. Thanks.