SOLVED

# Help Finding (Difficult?) Formula

Copper Contributor

# 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!

8 Replies

# Re: Help Finding Formula

@Josh_Smith505 Perhaps in F1:

``=MAX(E1,E2)``

# Re: Help Finding Formula

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.

# Re: Help Finding Formula

@Josh_Smith505 Sorry, don't follow.

# Re: Help Finding (Difficult?) Formula

If you know the math then you know the formula.

Else use Goal Seek.

# Re: Help Finding (Difficult?) Formula

@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.

best response confirmed by Josh_Smith505 (Copper Contributor)
Solution

# Re: Help Finding (Difficult?) Formula

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.

# Re: Help Finding (Difficult?) Formula

It's simple equation solving.

# Re: Help Finding (Difficult?) Formula

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!
1 best response

Accepted Solutions
best response confirmed by Josh_Smith505 (Copper Contributor)
Solution

# Re: Help Finding (Difficult?) Formula

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.