Solved

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

Posted on 2013-06-20
312 Views
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
0
Question by:pwflexner

LVL 8

Expert Comment

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

LVL 6

Accepted Solution

Michael earned 300 total points
ID: 39264381
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

LVL 80

Expert Comment

ID: 39264383
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

LVL 80

Expert Comment

ID: 39264389
Joop,
I like your formula better than mine.

0

Author Closing Comment

ID: 39264484
0

## Featured Post

### Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …