+ Reply to Thread
Results 1 to 33 of 33

A1: user defined number - wanting this to become apart of a formula?

  1. #1
    Forum Contributor
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    357

    A1: user defined number - wanting this to become apart of a formula?

    Hey all!

    Just quick one. Imagine A26 box is a user defined number. Say, 100.

    When someone places a number in there, I want this to in turn change the following formula:

    =(B27-A35)/A35

    Because someone put "100" in A26, I want this to now change the formula part "A35", to A135 -- (A35 + 100 = A135)?


    Can anyone help

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: A1: user defined number - wanting this to become apart of a formula?

    Please Login or Register  to view this content.
    So the result will be

    Please Login or Register  to view this content.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: A1: user defined number - wanting this to become apart of a formula?

    =(B27-INDIRECT("a"&35+A26))/INDIRECT("a"&35+A26) should sort it
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Forum Contributor
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    357

    Re: A1: user defined number - wanting this to become apart of a formula?

    Hmm...

    Thanks both of you, but it does not seem to be working?

    If I place: =("A"&35+A26) - it will just return "A135"... I actually want the value that sits within box A135 to be apart of the formula? (as opposed to just the text returning "A135")

    =(B27-A35)/A35 -- translates into = ( Larger number - original number ) / original number -- % gain.

  5. #5
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: A1: user defined number - wanting this to become apart of a formula?

    the indirect() does work i tested it

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,293

    Re: A1: user defined number - wanting this to become apart of a formula?

    Hi,

    Martin's solution seems to work OK for me - he just beat me to it

    Have you copied from Martin's post or typed it?
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  7. #7
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: A1: user defined number - wanting this to become apart of a formula?

    here it is working
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    357

    Re: A1: user defined number - wanting this to become apart of a formula?

    Ah I see from your spreadsheet.

    "A26" has the user defined number though? For a little more transparency:

    A26: I put in "120" ---> This means: A35 (fixed) + 120 = A155 in the formula below where $10,000 is...
    B27: $13,500

    =( $13,500 -( $10,000 )) / ( $10,000 )

    Hope that makes sense? I'm being a little slow probably with trying to rewrite your formula :P

  9. #9
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: A1: user defined number - wanting this to become apart of a formula?

    just try to see how it works in B1 put
    =INDIRECT("A"&10+A1)
    then put 100 in A10 and 500 in A30
    b1 will return 100 ie the formula references a10 a&10+0 =A10
    now enter 20 in a1 the formula will now reference a30 and return 500 (a&10+20 =a30)

  10. #10
    Forum Contributor
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    357

    Re: A1: user defined number - wanting this to become apart of a formula?

    Nice one - I get it now

    How would I use =INDIRECT("A"&36+A26) when looking at a range??

    I.e. A26:=INDIRECT("A"&36+A26) ?

  11. #11
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: A1: user defined number - wanting this to become apart of a formula?

    well you cant put it in the same cell! A26:=INDIRECT("A"&36+A26)
    but if you want to create a range using indirect
    =INDIRECT("A10:A"&A26) where A26 contains 20 would resolve to a10:a20

  12. #12
    Forum Contributor
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    357

    Re: A1: user defined number - wanting this to become apart of a formula?

    =INDEX(drawdown((INDIRECT("Z36:Z"&A26),A26:INDIRECT("A"&36+A26))),2,3)

    Like this? (CTRL+SHIFT enter)

    Original:

    =INDEX(drawdown(Z36:Z136,A36:A136),2,3)

    Thanks for your help and everyone elses input! :D

  13. #13
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: A1: user defined number - wanting this to become apart of a formula?

    i have absolutely no idea what that formula is! what is drawdown?

  14. #14
    Forum Contributor
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    357

    Re: A1: user defined number - wanting this to become apart of a formula?

    It just shows the maximum change from highest balance to consecutive low.

    I'm just wondering how I apply the indirect function?

  15. #15
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,293

    Re: A1: user defined number - wanting this to become apart of a formula?

    Quote Originally Posted by domgilberto View Post
    It just shows the maximum change from highest balance to consecutive low.

    I'm just wondering how I apply the indirect function?
    Hi,

    If 'drawdown' is a User Defined function then it is incumbent on you to at least mention it in your original post.

  16. #16
    Forum Contributor
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    357

    Re: A1: user defined number - wanting this to become apart of a formula?

    I appreciate that...

    But could I just not get an answer irrespective of what the function is...

    In one respect, just forget that function altogether.

    Lets say it was this: =AVERAGE(INDIRECT("Z36:Z"&A26)) - would this be correct?

  17. #17
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,293

    Re: A1: user defined number - wanting this to become apart of a formula?

    Quote Originally Posted by domgilberto View Post
    I appreciate that...

    .....
    Lets say it was this: =AVERAGE(INDIRECT("Z36:Z"&A26)) - would this be correct?
    Indeed so, as Martin said in post # 11. Did you try it?

  18. #18
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: A1: user defined number - wanting this to become apart of a formula?

    or
    =AVERAGE(Z36:INDEX(Z:Z,A26))

  19. #19
    Forum Contributor
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    357

    Re: A1: user defined number - wanting this to become apart of a formula?

    Ah spot on. Yea it's working I was being a little slow!

    I have one more question then I think this thread is resolved:

    If I wanted to use precisely this formula within a line chart: =INDIRECT("A35:A"&(A25+35)) - how can I do this?

    To reiterate - The user comes on the spreadsheet and puts in 100 within "A25"... This then looks up the range A35:A135 and then subsequently populate the line chart?


    Thanks for everyones patience :P

  20. #20
    Forum Contributor
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    357

    Re: A1: user defined number - wanting this to become apart of a formula?

    Any idea's?

  21. #21
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,293

    Re: A1: user defined number - wanting this to become apart of a formula?

    Hi,

    Why not just create a dynamic range name, say "MySeriesRange' which is defined as

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then in the chart define the series as

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  22. #22
    Registered User
    Join Date
    07-10-2014
    Location
    East Sussex, England
    MS-Off Ver
    2007, 2010
    Posts
    51

    Re: A1: user defined number - wanting this to become apart of a formula?

    Quote Originally Posted by domgilberto View Post
    How would I use =INDIRECT("A"&36+A26) when looking at a range??
    I.e. A26:=INDIRECT("A"&36+A26) ?
    Could you use this formula? You could extend the range in the first argument to the INDEX formulae as necessary.

    Please Login or Register  to view this content.
    Then when you change A26, which must be a minimum of 1 for it to work, it will calculate your division sum based on the cells further down the column.

    HTH

    Robbo.

  23. #23
    Forum Contributor
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    357

    Re: A1: user defined number - wanting this to become apart of a formula?

    Quote Originally Posted by Richard Buttrey View Post
    Hi,

    Why not just create a dynamic range name, say "MySeriesRange' which is defined as

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then in the chart define the series as

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I'm a little confused with this - can you correct me where I am going wrong?

    I have done what you've said and placed in an empty box: =OFFSET('Basic Monte Carlo Simulation'!A35,0,0,'Basic Monte Carlo Simulation'!B26+35)

    The "User Defined Entry" box is: B26 - in here someone can put any random number. This random number I want the line chart to use in selecting the range from A35 + "User Defined Entry" = X

    I have "Select Data..." under line chart settings and put in: ='Basic Monte Carlo Simulation'!$B$26


    It is just returning "1" with no line chart irrespective of what number I put into B26 (user defined entry)?

  24. #24
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,293

    Re: A1: user defined number - wanting this to become apart of a formula?

    Hi,

    Not clear exactly what you mean when you say you've placed gthe Offset formula in an empty box.

    You need to create a name using the Formulas... Name Range Manager menu item. Pick a range name (I suggested 'MySeriesRange' but name it whatever you want - obviously not one of the standard pre-defined function names) and define the name with that Offset formula I gave you.

    Then in the chart add a series and instead of entering the range address using cell references use the name you've defined.

  25. #25
    Forum Contributor
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    357

    Re: A1: user defined number - wanting this to become apart of a formula?

    I think I'm following you. However, I'm still doing something wrong here...

    =OFFSET($A$35,0,0,COUNT(INDIRECT("A35:A"&B26+35)))

    Am I CTRL+SHIFT+Entering?

    Check this screen out: http://screencast.com/t/yDKaxSvHakc

  26. #26
    Forum Contributor
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    357

    Re: A1: user defined number - wanting this to become apart of a formula?

    I think I'm following you. However, I'm still doing something wrong here...

    =OFFSET($A$35,0,0,COUNT(INDIRECT("A35:A"&B26+35)))

    Am I CTRL+SHIFT+Entering?

    Check this screen out: http://screencast.com/t/yDKaxSvHakc

  27. #27
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,293

    Re: A1: user defined number - wanting this to become apart of a formula?

    Perhaps you'd better upload the workbook....

  28. #28
    Forum Contributor
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    357

    Re: A1: user defined number - wanting this to become apart of a formula?

    Please find it attached
    Attached Files Attached Files

  29. #29
    Forum Contributor
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    357

    Re: A1: user defined number - wanting this to become apart of a formula?

    Any news ?

  30. #30
    Forum Contributor
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    357

    Re: A1: user defined number - wanting this to become apart of a formula?

    Did you get a chance to look at that Richard?

  31. #31
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,293

    Re: A1: user defined number - wanting this to become apart of a formula?

    Hi,

    See the chart I have overlaid on top of your original.
    Attached Files Attached Files

  32. #32
    Forum Contributor
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    357

    Re: A1: user defined number - wanting this to become apart of a formula?

    Superb thank you.

    I am trying to emulate what you've done on my real spreadsheet and I cannot get the line-chart to populate anything?

    I'm right clicking on chart > Select Data and then in "Chart Data Range" I am putting ='Basic Monte Carlo Simulation'!Myseries, but nothing is populating there?

    I've gone to formula's tab > Name Manager > New > Named it "Myseries" > =OFFSET('Basic Monte Carlo Simulation'!$A$35,0,0,'Basic Monte Carlo Simulation'!$B$26+35,1)

    Nothing?

  33. #33
    Forum Contributor
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    357

    Re: A1: user defined number - wanting this to become apart of a formula?

    Ah! Done it!

    Thank you very much for your help! Massively grateful!

    And thanks for everyone elses input too :D

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Confusing problem: Retrieving cell values based on user defined number range.
    By Andee in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-02-2014, 10:43 AM
  2. [SOLVED] Wanting to send a formula to a Cell using VBA and a variable sheet name defined with code.
    By sdavison in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-12-2013, 04:50 PM
  3. Excel user wanting to learn vba
    By MzADM in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 01-15-2013, 07:31 PM
  4. using MAX formula for user-defined number of cells
    By maacmaac in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-03-2011, 12:21 AM
  5. user defined function row number
    By bj in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-09-2005, 10:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1