# 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
###### Who is Participating?

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

Commented:
I've always done it the quick and dirty way, by using the second column for calculation, then hiding it.
0

Commented:
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

Commented:
Joop,
I like your formula better than mine.

0

Author Commented: