How Do I repeat a calculation until I cross a threshold in Google Sheets
I have a Google Sheet I'm using to evaluate financial goals. In this sheet, I have 8 cells. The first two cells are used to enter parameters that are used by the formulas in the other cells.
The first cell is a parameter that generates the result in cell 7. The second cell is a threshold I want to reach. Basically, I want to take the value in cell 1 and increase it by 1 until the result seen in cell 7 crosses the value in cell 2. An example looks like this:
A B
1 Current Total $10000
2 Target Amount $1000
3 Current Amount =DIVIDE(B1, 100)
4 Result A =PRODUCT(B3, 0.35)
5 Result B =PRODUCT(B3, 0.50)
6 Result C =PRODUCT(B3, 0.15)
7 TOTAL =SUM(B4:B6)
8 Total Needed ?
Using the example above, I want to create a formula that increases the value B1 by 1 until the value in B7 crosses the value in B2. I want to put the amount used to cross the threshold in cell B8.
I would prefer to do this repeat operation "behind the scenes" so only the result appears in cell B8. I don't want to show all of the "work". Is there a way to do this in a Google Sheet? If so, how?
Solution 1:
A little bit late, but to whom it may concern. Total Needed will hold value
=(B1 * (B2/B3)) - B1
I would prefer to do this repeat operation "behind the scenes" so only the result appears in cell B8. I don't want to show all of the "work". Is there a way to do this in a Google Sheet? If so, how?
This is achievable with Menu -> Extensions -> Google Apps Script - > Add Custom function. But anyway you and anybody will see nothing from the calculation because its enough fast.
Variant 1 My suggestion is to solve this by calculation formula which calculates the amount of times, needed to add 1
B3 is 1% of Current Total. Divide Target Amount by Current Amount to take 1% of Target Amount. Its simple math.
B8 = B2/B3
Multiply the result by Current Total. This will give how many times 1% of Target Amount has in Current Total
B8 = (B2/B3) * B1
Next, substract the Current Total from the result. This is needed because we need to know - how many times we have to add 1, after the added value of Current Total,
until the result in cell 7 crosses the value in cell 2
B8 = ((B2/B3) * B1) - B1
A B
1 Current Total 10000
2 Target Amount 1000
3 Current Amount = B1 / 100
4 Result A = B3 * 0.35
5 Result B = B3 * 0.50
6 Result C = B3 * 0.15
7 TOTAL = SUM(B4:B6)
8 Total Needed = ((B2/B3) * B1) - B1
If you have similar case which is difficult to solve - try to give good names of the properties it has. Let the names talk about the values they hold. This will help while thinking over it.
Exclude all values that have already been calculated.
Current Amount is 100% of Current Total, B3 = B1/100. So think about, this cell will always hold that value whenever you calculating in that case - it will always be =B1/100.
Results A, B and C are same as Current Amount. They are parts from Current Total, too. Result A is 35% from Current Total... and so on the others. Each of them will always have the same value.
TOTAL will always be equal to Current Amount, because it summaries the parts of it. Its a hardcoded value, too.
Now, separate known properties from the others. They all represent same one value - the parts of the Current Total - and you don't have to think about their calculation, because they have hardcoded values.
A B
1 Current Total
2 Target Amount __________________
3 Current Amount = B1 / 100
4 Result A = B3 * 0.35
5 Result B = B3 * 0.50
6 Result C = B3 * 0.15
7 TOTAL = SUM(B4:B6)
8 Total Needed __________________
Now, values left to work are - Current Total and Target Amount.
Using the example above, I want to create a formula that increases the value B1 by 1 until the value in B7 crosses the value in B2. I want to put the amount used to cross the threshold in cell B8.
Variant 2 Increase the value of B1 by 1, only by yourself, manually.
Test with D1 in column C, when C7=C2 the result will populate C8, else you have to increase, or decrease D1:
A B C D
1 Current Total 10000 = B1+D1 --> 90000 <--
2 Target Amount ____________ 1000
3 Current Amount = B1 / 100 = C1/100
4 Result A = B3 * 0.35 = C3*0.35
5 Result B = B3 * 0.50 = C3*0.5
6 Result C = B3 * 0.15 = C3*0.15
7 TOTAL = SUM(B4:B6) = SUM(C4:C6)
8 Total Needed ____________ = IF(C7=C2; D1; "Change D1")