Calculating percentage drops in a column of numbers using the Small command

I have a column of numbers (column A), a sample of which is below.  What I need a to calculate in a single cell  (B1) is the largest percentage drop from any given value to the lowest subsequent value.  I can easily do this using another column to calculate the percentage drop, but I want to do it within one cell.  I want to use Small so I can cput the second largest drop in cell B2, etc.  Is this possible?

Here's the sample data:

A            B
1000      -5.66%    (from 1060 to 1000)
1025      -5.58%    (from 1165 to 1100)
1060
1000
1036
1084
1165
1100
1180
1240
1225
pwflexnerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
MichaelConnect With a Mentor Business AnalystCommented:
Put this in B1,
enter as an array formula (so, confirm with ctrl+shift+enter, instead of just with enter)
and drag down:
=SMALL((A$2:A$11/A$1:A$10)-1,ROWS(B$1:B1))

assuming your data runs from A1 to A11, but you can change this in the formula accordingly

Joop
0
 
stevepcguyCommented:
I've always done it the quick and dirty way, by using the second column for calculation, then hiding it.
0
 
byundtCommented:
Array-enter the following formula:
=SMALL((A$2:A$11-A$1:A$10)/A$1:A$10,ROWS(B$1:B1))
PercentChangeQ28163340.xlsx
0
 
byundtCommented:
Joop,
I like your formula better than mine.

Brad
0
 
pwflexnerAuthor Commented:
Thanks to all for your responses.JazzyJoop's answer worked best for me.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.