What IF Analysis – Limitations of Spin Bar, Scroll Bar

4 minutes
Share the link to this page
You need to purchase the class to view this lesson.
One-time Purchase
List Price:  $139.99
You save:  $40
List Price:  د.إ514.18
You save:  د.إ146.92
List Price:  A$195.22
You save:  A$55.78
List Price:  ৳11,874.82
You save:  ৳3,393.05
List Price:  CA$185.28
You save:  CA$52.94
CHF 91.10
List Price:  CHF 127.54
You save:  CHF 36.44
List Price:  kr884.03
You save:  kr252.59
List Price:  €118.72
You save:  €33.92
List Price:  £107.32
You save:  £30.66
List Price:  HK$1,085.03
You save:  HK$310.03
List Price:  ₹10,472.62
You save:  ₹2,992.39
List Price:  RM587.04
You save:  RM167.74
List Price:  ₦53,336.19
You save:  ₦15,240
List Price:  kr1,250.97
You save:  kr357.44
List Price:  NZ$212.50
You save:  NZ$60.71
List Price:  ₱6,854.21
You save:  ₱1,958.48
List Price:  ₨23,539.31
You save:  ₨6,726
List Price:  S$192
You save:  S$54.86
List Price:  ฿4,349.48
You save:  ฿1,242.80
List Price:  ₺1,020.31
You save:  ₺291.53
List Price:  R2,440.10
You save:  R697.22
Already have an account? Log In


Greetings everyone, we follow up this exercise from our previous video which we talked about in terms of how to create scroll bar and spin button. But mind you, if you are creating this tool bar, it comes with one limitation. I'll show you what that limitation is. And I will also show you how to overcome that limitation. I'm going to the Insert button under Developer tab and going to the second row and finally the third button which we refer as toolbar. Now within scroll bar, if I try to create a scroll bar and trying to link this toolbar to the first yellow cell, which is indicating interest rates, so I say minimum value zero percentage maximum, let's say 20%.

And incremental should be 1%. As a priest, okay, and click outside the button to make it go live. And I click on the edge of the button, notice nothing is happening. Why? Because the buttons are not made to work. With decimal numbers, not it is made to work with numbers beyond 30,000.

And at the same time, it can't work with negative numbers. So if I tried to configure this button to control the second yellow cell, let's say in this case be six and minimum value zero maximum, let's say 40 lakh even more than that. And at times I would want to keep any incremental change as $50,000. Okay, says school value must be between zero and 30,000. So on occasion, they are making a financial model where you need to manipulate numbers, which may be a very large number, let's say home prices, or maybe interest rates, which are in decimals. How do you create that button?

I'll give you a workaround. In such cases, what you need to do is create dummy cells. What are dummy cells, the dummy cells are going to work like this. I'm going to put a number which is your 10 In the yellow cell, I will write a formula which is equal 10 divided by 100. So effectively as the cell, which is C two changes to, let's say 20, the percentage automatically turns to 20%. I can already guess that you might be thinking of the solution, which I'm going to target that is you click the button, and the scroll bar is going to manipulate not the yellow cell, but the white cell.

And once the white cell changes, yellow cell changes automatically. So cell link is the yellow and minimum is zero. I'm fine with that and maximum let me keep it 100 incremental stage one. Okay. So as I click on the button, it changes and Whitesell eventually changes the yellow cell As you can notice, so this is how it is overcome in financial models. A similar thing we will try to achieve for the next case study.

So I write 30,000 Okay, I write a number which is going to multiply this 30,000 with hundred, which indicates as 3 million. Now I go to developer, I go to Insert, second to third buttons toolbar, I click on that I make a button, right click Format control. Now the selling is going to point to the wide sell and you already know that the maximum school value can be 30,000. So we write this as 30,000. Okay, and incremental change, it could be 10 fine, invest Okay. Now, as I click on it, notice it is changing the white cell which is in increments of 10 and that is getting multiplied with thousand.

So this way, you can find the multiplier factor and configure the button to change the white cell which in turn will change the yellow cell. So keep practicing. These are small tips and tricks which are going to help you make anything To get financial models

Sign Up