• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Switching variable from dependent to independent

J

Jspencer

New Member
I am thinking about a model where I could have three main inputs: New investment/% owned of the company/price paid by new investor

2 of the 3 variables can be independent variables, while the 3rd must be a dependent defined by the two chosen independents. I would like to be able to change which two of the three can be independent's depending on how the negotiations are developing.

For example in one scenario it might be best to have:
Dependent Variables: A1, A2
Independent Variable: A3

In the next example it might be beneficial to have:
Dependent Variables: A1, A3
Independent Variable: A2

Many Thanks in Advance
 
Hi, Jspencer!

As a new user you might want to (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, give a look at the uploaded file. Is that what you were looking for? If not, then consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you, relieving the contributor of having to build a test file, if necessary. Thank you.

Regards!
 

Attachments

  • Switching variable from dependent to independent (for Jspencer at chandoo.org).xlsx
    9.1 KB · Views: 4
Hi SirJB7, thanks for taking a look at this. I've uploaded the sheet that I'm trying to manipulate. Cells C83-85 are the ones that I would like to set as the dependent/interdependents with the ability to change which one is dependent.

Let me know if this is something that is possible.
 

Attachments

  • Cap Table Modelling R6.xlsx
    37.8 KB · Views: 6
Hi, Jspencer!

I don't see what do you want to do. In range C83:C85 only C85 has a formula, with dependencies in C83 and E75. So elaborate and explain in detail all the possible scenarios and what do you want to achieve. Something similar to what you I did in my uploaded file.

Regards!
 
Narayan wow that looks awesome. Yes that is exactly what I'm trying to do, however I'm having problems following the arrays. You wouldn't happen to be interested in doing a skype call, or if I were to phone call would you?
 
Hi Spencer ,

If you want me to put in the same formulae into your file , let me know. I'll do it and upload the file.

Narayan
 
Narayan, thank you so much. This is very kind of you to do. I have included how to calculate each cell on rows 88-90. The calculations will be needed when one of the three becomes the dependent.
 

Attachments

  • Cap Table Modelling R7.xlsx
    38 KB · Views: 2
Hi Spencer ,

From what I have seen , the dependencies are as follows :

D81 is dependent on C83.

D74 and E74 are dependent on C84.

C85 is dependent on C83 and E75 , which means C85 is dependent on C83 and C84.

C83 is dependent on C85 and E75 , which means C83 is dependent on C84 and C85.

Thus C84 is independent ; either of C83 or C85 could be a user-entered value ; if C83 is entered , then C85 is calculated , and if C85 is entered , then C83 will be calculated.

Can you confirm / correct all of the above ?

Narayan
 
Hi Narayan, I have replied below:

D81 is dependent on C83. - Correct

D74 and E74 are dependent on C84. - D70:74 & E70:74 are dependent on C84

C85 is dependent on C83 and E75 , which means C85 is dependent on C83 and C84. - Correct

C83 is dependent on C85 and E75 , which means C83 is dependent on C84 and C85. - Correct

Thus C84 is independent ; either of C83 or C85 could be a user-entered value Correct ; if C83 is entered , then C85 is calculated (I would like to have the option to hard enter C83 and C85 at the same time and have C84 be automatically calculated as a result), and if C85 is entered , then C83 will be calculated.(Same as previous)
 
Hi Spencer ,

This means one option would be to enter C83 and C85 , and have C84 be automatically calculated.

What about the other options ? Are there any ?

If you say that C84 is to be calculated , then what would the formula be ? Should I get the formula going through your worksheet , or do you have it ready ? If so , can you post it ?

Narayan
 
The three combinations I'm looking for are as follows:

If C84 & C85 are inputs then C83 automatically calculates as: =C85*E75
If C83 & C84 are inputs then C85 automatically calculates as: =C83/E75
If C83 & C85 are inputs then C84 automatically calculates as: =E75/E76
 
Hi Spencer ,

It doesn't work that way !

As we have already seen D74 and E74 are dependent on C84 ; so unless C84 is already in place , the above calculation will return 0.

If we really need to calculate C84 given C83 and C85 , we need a more complicated formula.

Narayan
 
I'm going to look to see if I can figure out a more complicated formula to calculate C84..do you have any ideas?

Basically what you're saying is I can't create a formula for C84 that includes D74 and E74 (I think you mean D75 and E75?) or any other numbers that are dependent on D74 and E74?
 
I think D75 and E75 need to be calculated an alternative way when C84 becomes a dependent variable. D75 and E75, will need to have alternate formulas to calculate based on which of the three potential inputs (C83:85) are the independents and which is the dependent...does that sound feasible and seem like it could work?

My apologies if I'm not explaining this right. It's a bit tricky.
 
When C84 is a variable the formulas in D75 and E75 no longer work. So D75 and E75 will need to need to switch in the circumstance when C84 is dependent to use another formula. In these cases E75 would use the following formula to calculate: C83/C85
D75 would calculate by: E75/E76
and
C84 = D75

When C84 is not a dependent D75 and E75 would use the existing formulas in their cells
 
Hi Narayan, is this something you are still willing to help me with? Thank you for all that you have done up until now.
 
Hi Spencer ,

Sorry about that , but we are in different time zones.

I had gone through your worksheet yesterday , and this is what I came up with for the three options ; can you confirm that this is correct ? If yes , then I think a macro will be a better option , since putting these calculations using formulae might be somewhat complicated.

Option 1 : User enters C83 and C84.

D75 becomes =C84
E75 becomes =SUM(C70:C74)*D75/(1-D75)
C85 becomes =C83/E75

Option 2 : User enters C83 and C85.

E75 becomes =C83/C85
C84 becomes =E75/E76
D75 remains =C84

Option 3 : User enters C84 and C85.

D75 remains =C84
E75 becomes =SUM(C70:C74)*D75/(1-D75)
C83 becomes =E75*C85

Narayan
 
Good Morning Narayan,

Option 1 : User enters C83 and C84.

D75 becomes =C84 - Correct
E75 becomes =SUM(C70:C74)*D75/(1-D75) - Correct
C85 becomes =C83/E75 - Correct

Option 2 : User enters C83 and C85.

E75 becomes =C83/C85 - Correct
C84 becomes =E75/E76 - Correct
D75 remains =C84 - Correct

Option 3 : User enters C84 and C85.

D75 remains =C84 - Correct
E75 becomes =SUM(C70:C74)*D75/(1-D75) - Correct
C83 becomes =E75*C85 - Correct
 
Narayan of course I can. I will need it though in about 3 hours. I can always make multiple sheets if you feel that this agenda is too fast
 
Hi Spencer ,

Not at all. What you mention is an easy workaround ! If it is a viable option , why not use it ? If it is not viable , then I'll certainly upload your file with the macro in some time.

Narayan
 
You must log in or register to reply here.
Back
Top