Forum Discussion
Help Finding (Difficult?) Formula
Hello,
I want F1 to show what value A1 would have to be for E1 to match E2.
A1 represents monthly price charged.
B1 represents initial investment cost.
C1 represents months in a year.
D1 represents earnings from monthly price charged over 12 months
E1 represents crude ROI example.
I want F1 to show what A1 price would have to be for E1 to match the 27% in E2 (rounded up). Is there a formula to enter into F1 that would show this new value WITHOUT changing the cell data below? (For example, if A1 = $425 and all other cells were kept the same, THEN E1 would change from 19% to the desired 27% assuming you round up from the resulting 26.84% of course --- BECAUSE 425*12 = 5,100 / 19,000 = ~26.84)
For context, D1 =A1*C1 and E1 =D1/B1
Many thanks!
Refer to the following image:
If E1 = D1/B1 should equal E2, then with simple algebra, the formula in D1 should be =B1*E2.
And if D1 = A1*C1, then with simple algebra, the formula in A1 should be =D1/C1.
But note that A1 is not $425, and D1 is not $5100.
-----
OTOH, if A1 should be rounded down to a multiple of 5 (wild guess; you need to specify why you expect $425), then perhaps the formula in A1 should be =FLOOR(B1*E2/C1, 5), and the formula in D1 can be =A1*C1, as specified originally.
That results in the following:
But note that E1 cannot be exactly the goal in E2. (But for this example, it does round to E2.)
-----
Alternatively, if D1 should be rounded to a multiple of $100 (again, a wild guess; you need to specify), then the formula in D1 should be =MROUND(B1*E2, 100), and the formula in A1 might be =D1/C1 again.
That results in the following again:
But note that again E1 cannot be exactly the goal in E2.
8 Replies
Refer to the following image:
If E1 = D1/B1 should equal E2, then with simple algebra, the formula in D1 should be =B1*E2.
And if D1 = A1*C1, then with simple algebra, the formula in A1 should be =D1/C1.
But note that A1 is not $425, and D1 is not $5100.
-----
OTOH, if A1 should be rounded down to a multiple of 5 (wild guess; you need to specify why you expect $425), then perhaps the formula in A1 should be =FLOOR(B1*E2/C1, 5), and the formula in D1 can be =A1*C1, as specified originally.
That results in the following:
But note that E1 cannot be exactly the goal in E2. (But for this example, it does round to E2.)
-----
Alternatively, if D1 should be rounded to a multiple of $100 (again, a wild guess; you need to specify), then the formula in D1 should be =MROUND(B1*E2, 100), and the formula in A1 might be =D1/C1 again.
That results in the following again:
But note that again E1 cannot be exactly the goal in E2.
- Sorry for not originally specifying that I did have the rounding built in. The FLOOR function works - thank you so much for your help and explanation!
If you know the math then you know the formula.
Else use Goal Seek.
Detlef_Lewin Thank you. However, I "know the math" because I manually typed and changed the A1 value until E1 matched E2 - so I don't really know the formula and it's not ideal when working with a large dataset.
Hi Riny_van_Eekelen,
Thank you for your reply. I edited original post for clarity. The suggested MAX formula resulted as $0 and does not work.Josh_Smith505 Sorry, don't follow.
