Solved

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

Posted on 2013-06-20
5
312 Views
Last Modified: 2013-06-20
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
Comment
Question by:pwflexner
5 Comments
 
LVL 8

Expert Comment

by:stevepcguy
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

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

by:byundt
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

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

Brad
0
 

Author Closing Comment

by:pwflexner
ID: 39264484
Thanks to all for your responses.JazzyJoop's answer worked best for me.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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 …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now