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

1. ## 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. ## 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.``

3. ## 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

4. ## 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. ## Re: A1: user defined number - wanting this to become apart of a formula?

the indirect() does work i tested it

6. ## 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?

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

here it is working

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

"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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: A1: user defined number - wanting this to become apart of a formula?

Originally Posted by domgilberto
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. ## 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.

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

Originally Posted by domgilberto
I appreciate that...

.....
Indeed so, as Martin said in post # 11. Did you try it?

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

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

19. ## 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

Any idea's?

21. ## 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:
`Please Login or Register  to view this content.`

Then in the chart define the series as

Formula:
`Please Login or Register  to view this content.`

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

Originally Posted by domgilberto
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. ## Re: A1: user defined number - wanting this to become apart of a formula?

Originally Posted by Richard Buttrey
Hi,

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

Formula:
`Please Login or Register  to view this content.`

Then in the chart define the series as

Formula:
`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. ## 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. ## 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. ## 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. ## Re: A1: user defined number - wanting this to become apart of a formula?

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

Any news ?

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

Did you get a chance to look at that Richard?

31. ## 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.

32. ## 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. ## 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

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

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