Solved

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

Posted on 2013-06-20
5
321 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 81

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 81

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

948 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

21 Experts available now in Live!

Get 1:1 Help Now