How do I get a specific outcome from a function?

**Platerising** · 01-10-2016, 08:54 PM

This might be a newbie question. I have a function and its outcome. I wan't the outcome to be a specific number. Is there any way to make excel adjust the right numbers for the cells that are in the function, so that I get the specific outcome?

An example. A1's value is 10, A2's value is also 10. In A3 I write =sum(A1:A2), of course I get 20. But let's say that I wanted to make excel find what value would need to be in A1 so that the outcome of the function in A3 becomes 30 (I have no idea the answer is 20).

Any way to do this?

**Norie** · 01-10-2016, 09:03 PM

Couldn't you use simple subtraction?

In A3 you could have this formula.

Formula:

Please Login or Register  to view this content.

**FDibbins** · 01-10-2016, 09:06 PM

If you have the answer and 1 of 2 numbers, shouldnt it be a simple matter to just reverse the calc? =A1-30 (or A2-30)
?

**Platerising** · 01-10-2016, 10:17 PM

It's just an example, was trying to explain what I'm trying to do.

I'm asking because I'm creating a model for a fixed rate amortisation schedule, for a mortgage. The loan is set for 40 years, but I can pay a certain amount the principal every month and reduce the period of the loan. I have made a function for the years it would take me to pay up the loan. As I raise the amount I pay into the principal every month the number of years go down.

Now I want to find the exact amount I need to pay to make the period of the loan 20 years. I don't want to guess the amount, is there any way I can make excel find it for me?

**FDibbins** · 01-10-2016, 10:31 PM

if you google excel amortization formula, you get a few good hits, see if 1 of those will help you?

**TMS** · 01-10-2016, 10:34 PM

Given all the financial functions available in Excel, I would have thought there would be one you could use:

https://support.office.com/en-us/art...1-fbf124c2b1d8

Regards, TMS

**Platerising** · 01-12-2016, 05:17 PM

Found it, I was looking for Goal Seek all along.

**newdoverman** · 01-12-2016, 05:29 PM

Solver could also be a choice and allow you to have control over both A1 and A2 by using constraints.
Eg. A3 has =SUM(A1:A2)
You want the sum to be 30 and A2 to be half of A1. You would set the constraint to be A1=A2/2 and using Simplex LP as the solving method the answer would be
A1= 20
A2= 10

How do I get a specific outcome from a function?

LinkBack

Thread Tools

Rate This Thread

Display

How do I get a specific outcome from a function?

Re: How do I get a specific outcome from a function?

Re: How do I get a specific outcome from a function?

Re: How do I get a specific outcome from a function?

Re: How do I get a specific outcome from a function?

Re: How do I get a specific outcome from a function?

Re: How do I get a specific outcome from a function?

Re: How do I get a specific outcome from a function?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] If function, many ifs but one or two outcome?

IF Function returning text as outcome

[SOLVED] IF Statement which includes an outcome only if specific criteria are met

Function to write Data to DB from specific rows and colomuns Plus specific Timestamp

[SOLVED] outcome of CONCATENATE function

[SOLVED] If function Multiple logic tests each with its own outcome. How do I write it.

Linking the outcome of an IF function to a vlookup.

Bookmarks

Bookmarks

Posting Permissions