Percentage Change Between a range of numbers? Please help :(

[domgilberto]

Hi All,

Hoping some clever people can help me


Pretend their are two Tabs in excel.

Tab 1:

"A1" contains a random number: "20"

Tab 2:

Column "A" has tonnes of random numbers:

6000 - Starting Balance (changeable)
5890
5800
5950
6080
6100
6180
6250
6300

an so on... (1,000 rows)


Based upon the number "20" that I placed in A1 on Tab 1, I want a formula that looks at the first 20 rows in column A within Tab 1, and then calculate the percentage change between the extreme "peak to trough" of the highest number to the lowest number within the 20 rows...

Below should make it really simple:

6000 - Starting Balance (freely changeable)
5890 -1.83%
5800 -3.33% - This was the lowest percentage change from the starting balance of 6,000
5950 -0.83%
6080 +1.33%
6100 +1.67%
6180 +3.00%
6250 +4.17%
6300 +5.00%
And so on...


I hope this make sense! It's been winding me up for a while becaus I have no idea what the formula is


Thanks in advance!

[Pepe Le Mokko]

Perhaps

Please Login or Register  to view this content.

placed in sheet 2

[domgilberto]

Forgive me (for I am useless with excel) but this formula looks really interesting; Its just, i'm really confusing myself now because I gave an example above and I have an in-depth spreadsheet I need to apply this to... Just cant figure out how lol.

The number "20" (which can change on the fly based upon another formula and contains a formula) is D4 on "Graph 3" sheet...

All the numbers are in "Outcome 3" and run 1,000 rows down in column "A"...

I want this formula to populate the result in "D31" on "Graph 3"...

Sorry if I'm being difficult! Appreciate your time!

[domgilberto]

=A3/MAX($A$2:A2)-1

This is it - thanks for your help anyway bud!