Different formulas based on a value

[Fantrasp12]

Hi, so I'm having a relaly hard time here. I'm building a cap table (shares vesting) but based on different scenarios. For example, 4 years with 1 year cliff (and no cliff), 3 years with 1 year cliff, and so on. I have all the different formulas working independently, but i don't want people in my company to have to choose and manually copy/paste a formula each time (will confuse people and prone to error). Ideally i can just have a code in a cell that represents what formula to use. For example, you can see in the exhibit below, The formulas work in column N, example =IF(DATEDIF(E2,F2,"D")<365,0,MIN(DATEDIF(E2,F2,"M")/48*H2,H2)), but this is based only on a 48 month vesting schedule with no cliff. I did try to make 2 arguments with 36 and 48 month vesting with an IF using the code in column L to read from (see formula exhibited below which represents O2, But, it doesnt work. I either get "FALSE" if i use "IFS", i get invalid if i use "IF", and i've tried so many combinations of parenthesis that won't make it work. I've also tried "CHOOSE function, to no avail. I suspect its the nested arguments within one another that need more nuance.

FYI, so it will be a pretty long formula given the scenarios i have to cover. For example:

 

[myall_blues]

One way, which I think would be better for maintenance than a very long and complex formula, would be to make use of some of the sheet real estate to calculate every value in a separate column, and then use HLOOKUP to get the correct answer by looking up the code. You could even put the calculations on another sheet and hide it if you really wanted.

Like so:

Book1
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Award date	Today's date		# Shares				Code		Current Vested		31	41
2		1/01/2021	8/05/2024		19				31		19		19	15.83
3		1/01/2021	8/05/2024		19				41		15.83		19	15.83
Sheet1

Cell Formulas
Range	Formula
P2:P3	P2	=IF(DATEDIF(E2,F2,"D")<365,0,MIN(DATEDIF(E2,F2,"M")/36*H2,H2))
Q2:Q3	Q2	=IF(DATEDIF(E2,F2,"D")<365,0,MIN(DATEDIF(E2,F2,"M")/48*H2,H2))
N2:N3	N2	=HLOOKUP(L2,$P$1:$Q2,ROW(),FALSE)

 

[Fantrasp12]

One way, which I think would be better for maintenance than a very long and complex formula, would be to make use of some of the sheet real estate to calculate every value in a separate column, and then use HLOOKUP to get the correct answer by looking up the code. You could even put the calculations on another sheet and hide it if you really wanted.

Like so:

Book1
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Award date	Today's date		# Shares				Code		Current Vested		31	41
2		1/01/2021	8/05/2024		19				31		19		19	15.83
3		1/01/2021	8/05/2024		19				41		15.83		19	15.83
Sheet1

Cell Formulas
Range	Formula
P2:P3	P2	=IF(DATEDIF(E2,F2,"D")<365,0,MIN(DATEDIF(E2,F2,"M")/36*H2,H2))
Q2:Q3	Q2	=IF(DATEDIF(E2,F2,"D")<365,0,MIN(DATEDIF(E2,F2,"M")/48*H2,H2))
N2:N3	N2	=HLOOKUP(L2,$P$1:$Q2,ROW(),FALSE)

Thanks. I'm not sure how that would exactly work though. My first effort was to do something like this but couldn't pull the formula in by using a code. I guess i could use a code to look the answer up. But i am trying to not burden the user to deal too much with where to enter codes, how it calculates (I'm not going to be the user). Are you saying that I would have another sheet that would just calculate all possible formulas and then the code would grab the one i want? If so I suppose this could work but seems like alot of work and use of real estate just to get one answer (and many that I don't need). Can you please conform this is the approach?

I think it would be much more compact and simpler to just find the answer right in the appropriate cell and avoid all unnecessary calculations and lookups. The formula is what i'm stuck on. I've done IFS, but the additional IF as part of the formula i think its not letting all arguments process from left to right completely. I'd appreciate thoughts on both approaches...thank you.

 

[myall_blues]

The sheet I attached is an example of how to do it. All your user needs to know is the code to put in column L, which you could supply by a dropdown.
The “code” in my example is in columns P and Q (as a column header). The HLOOKUP formula in column N just takes the code value in column L, looks it up in P1:Q1 ( or more columns depending on how many formulas you have) and returns the answer.
The calculations in columns P and Q can be shifted to another sheet to hide them.
Post your other calculations and codes and I’ll send a link to an example spreadsheet.

 

[myall_blues]

If you still want to go down the one formula route, then your IF statement is this (for just codes 31 and 41).

=IF(L2=31,IF(DATEDIF(E2,F2,"D")<365,0,MIN(DATEDIF(E2,F2,"M")/36*H2,H2)),IF(L2=41,IF(DATEDIF(E2,F2,"D")<365,0,MIN(DATEDIF(E2,F2,"M")/48*H2,H2)),"Undefined code"))

As you can see by the time you add another 7 formulas to this it's going to be pretty ugly.

A good way to approach it if you want to do this is to put each formula corresponding to each code in a cell, and then build the formula by referring to those cells initially. This will give you an intermediate step something like:

=IF(L2=31,P2,IF(L2=41,Q2,IF(L2=1A,<Formula if code is 1A>,"Message if no conditions are true")))

Once you have that working you can then copy/paste the respective formulas into their locations - for example, swap the formula in P2 for the reference to P2, giving:

=IF(L2=31,IF(DATEDIF(E2,F2,"D")<365,0,MIN(DATEDIF(E2,F2,"M")/36*H2,H2)),IF(L2=41,Q2,IF(L2=1A,<Formula if code is 1A>,"Message if no conditions are true")))

and so on.

 

[Fantrasp12]

=IF(L2=31,IF(DATEDIF(E2,F2,"D")<365,0,MIN(DATEDIF(E2,F2,"M")/36*H2,H2)),IF(L2=41,IF(DATEDIF(E2,F2,"D")<365,0,MIN(DATEDIF(E2,F2,"M")/48*H2,H2)),"Undefined code"))

THANK YOU. I did initially tried to have formulas switch based on codes, but determined (after hours) this isn't possible. After hours of trying and researching seems cells don't know how to handle this, only pull in the actual values form those cells (not the formula). At least that's what I concluded. Are you saying tha tit is possible?

As fir the long formula, thank you!. Looks like you just added the "undefined code")) at the end? What does that do? I don't mind the long formula as opposed to having to build an enormous table of hundreds of cells instead (would have to repeat all of this for future quarterly projections).

 

[myall_blues]

Are you saying tha tit is possible?

Yes, entirely possible.

Re the “undefined code”, every IF statement needs a TRUE clause and a FALSE clause. Since we are nesting multiple IF statements together, the last FALSE clause needs to handle the case where none of the preceding cases apply.
The actual text is irrelevant - it could say “pineapples” equally well.

 

[Fantrasp12]

Yes, entirely possible.

Re the “undefined code”, every IF statement needs a TRUE clause and a FALSE clause. Since we are nesting multiple IF statements together, the last FALSE clause needs to handle the case where none of the preceding cases apply.
The actual text is irrelevant - it could say “pineapples” equally well.

thanks, i think i get lost in the multiple nested parts, and how things i've gotten to work in the past much harder in com plex scenarios.

Would you mind pointing me to a link that explains how to get formulas to switch using a code? I spent hours trying to do this and all info i came up with was either irrelevant or said it couldn't be done. I'd like to save you the trouble so am happy to dig in where this is already explained at a detailed level. Many thanks!!!