• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Array Formula

P

pauldurso

New Member
I have two tabs in the document below (INCOME & DATA)


Link here: http://dl.dropbox.com/u/19969657/solve.xlsx


M7 on the INCOME tab manipulates the chart below it. I am looking to make I47 equal to zero. I have been changing cell M7 manually, but would like to write a formula that can automate it for me.


I have created an array of numbers on the DATA tab that starts with .01 and goes all the way up to 10.14. Each cell contains a separate number.


Is there a way to write a formula to take the array table from the DATA tab and place it in cell M7 on the INCOME tab (one cell at a time) until it finds the number that make cell I47 on the INCOME tab equal to 0 (zero)? If it can't equal zero exactly, then I just need it to get close.


The answer is F26 which is 2.00


I'm thinking the formula would have to find the first number that made cell I47 greater than zero. If you type any number between 0 and 1.99 in cell M7, then I47 will be negative. Once you type 2.00 or greater in M7, cell I47 turns positive.


I need a formula, not goal seek or solver or what if functions from excel.


Any thoughts or help would be greatly appreciated.
 
Paul

Have you seen the Data Table function?

Have a read of: http://chandoo.org/wp/2010/05/06/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide/
 
Paul


Excel has a Goal Seek function, follow along below


Select the Income Sheet

Goto the Data, What If Analysis, Goal Seek tab

Set Cell: I47

To Value: 0

By Changing Cell: M7

Apply


Excel will change cell M7 to 1.99603667205102%

and I47 is now $0.0


If you don't want to keep the Number in M7; Ctrl Z to undo it
 
You must log in or register to reply here.
Back
Top