Find the biggest 'downturn' in a column of positive and negative numbers

**Armstrong69** · 11-26-2017, 06:21 AM

Hello,

I have a long column of various numbers, positive and negative.
Is it possible to find out the value of the biggest overall loss in the column (i.e. the biggest downturn)?

5
4
-3
-5
2
-3
11
0
-8
50
-8
50

In the above example I would like a return value of 9 (the biggest downturn as seen in A3 to A6).
The column will always have an overall positive sum. I don't mind having an extra column of data if it makes things easier.
Hope that makes sense. I've attached an example sheet.

Any help would be much appreciated! Thanks.

**6StringJazzer** · 11-26-2017, 12:40 PM

How do you define "downturn"? What do these numbers mean?

In the file you attached you said the biggest loss was in A9:A13 for a net of -427 but if you sum A3:A13 you get -541, much larger loss. A19:A24 is -554. So it's not clear at all what you are trying to do.

**Armstrong69** · 11-26-2017, 05:37 PM

Sorry, you're right, I didn't give a proper set of numbers. How about this:

5
4
-3
-5
2
-3
112
0
-8
5000
-8
5000

I would like the largest overall loss, regardless of how many rows it covers. The overall sum of the column will always be positive.
The numbers are variables.

**6StringJazzer** · 11-26-2017, 10:12 PM

All the latest post does is provide a different list of numbers. I am looking for a definition (not only an example) of the result you are looking for. Like:

Find the subset of consecutive numbers in the list that sum to the highest-magnitude negative number.

But that's just an example...I'm still not sure what you mean.

P.S. If that is what you mean then I'm pretty sure it needs a macro. Not difficult, but I would like to solve this in one try.

**shivya** · 11-27-2017, 02:21 AM

Hello Armstrong,
I don't understand, what you want exactly but i think it should be like

=SUM(LARGE(A1:A12,{1;2;3}),SMALL(A1:A12,{1;2;3})) -> ctrl + shift + enter

**Armstrong69** · 11-27-2017, 02:23 AM

Imagine there is a running total. I want to find the largest loss that happens before the running total reaches a new maximum.
Does this pic help? In this example I would want the answer 20.

**AliGW** · 11-27-2017, 02:26 AM

How have you calculated 20? That run includes a positive number - how do you define a loss?

**Armstrong69** · 11-27-2017, 02:49 AM

Yes, it includes a positive number. 20 is the difference between the 'peak' of the running total (in the red circle) and the lowest point (also in the red circle) before the running total reaches a new maximum.

**José Augusto** · 11-27-2017, 12:18 PM

Hi
I do not Know if this can help you.

I use your first file and move your data three rows down

B2, B3 and C3 are zeros (initialize formulas) and run totals in B4

Use in C4 the following formula (copy down)

Formula:

Please Login or Register  to view this content.

The value (I suppose) is the minimum value of that column.

See the file

**6StringJazzer** · 11-27-2017, 01:03 PM

From your example I take it you mean a new global maximum rather than a local maximum (e.g., 40 on row 11 is a local maximum). I still think your definition is not rigorous from a mathematical standpoint but I am trying to grok what you are looking for. You still have not responded to my two requests for the background of what these numbers mean, but here is an attempt using a macro. This uses your most recent data posted as an image, because that seems to the be the best explanation of what you want.

The problem is to:

Examine each occurrence of a new maximum. A new maximum is defined as a point that is a global maximum compared to the points to its left.
Determine the loss preceding each new maximum. A loss is defined as the drop starting from the previous maximum to the previous local minimum. The previous local minimum is the point of the lowest value after the previous maximum and before reaching the new maximum.
Determine which of these losses has the largest magnitude.

In the future, consider that an image attachment has very little value. Just attach the Excel file. It's easier than taking a screenshot first and then attaching that. Under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

**Armstrong69** · 11-27-2017, 05:21 PM

I'm sorry I've done such a poor job of explaining what I'm after

I've solved it myself and attached an example sheet.

6StringJazzer: The numbers represent profit/loss projections that change according to a formula that I'm constantly reassessing.

Thanks all for your help, you forum mods certainly have a lot of patience

**6StringJazzer** · 11-28-2017, 11:18 AM

That's pretty good thinking. That's what I thought you were trying to do (now I see a bug in my solution) and you very elegantly solved it with formulas, which I didn't think possible. Bravo.

Find the biggest 'downturn' in a column of positive and negative numbers

LinkBack

Thread Tools

Rate This Thread

Display

Find the biggest 'downturn' in a column of positive and negative numbers

Re: Find the biggest 'downturn' in a column of positive and negative numbers

Re: Find the biggest 'downturn' in a column of positive and negative numbers

Re: Find the biggest 'downturn' in a column of positive and negative numbers

Re: Find the biggest 'downturn' in a column of positive and negative numbers

Re: Find the biggest 'downturn' in a column of positive and negative numbers

Re: Find the biggest 'downturn' in a column of positive and negative numbers

Re: Find the biggest 'downturn' in a column of positive and negative numbers

Re: Find the biggest 'downturn' in a column of positive and negative numbers

Re: Find the biggest 'downturn' in a column of positive and negative numbers

Re: Find the biggest 'downturn' in a column of positive and negative numbers

Re: Find the biggest 'downturn' in a column of positive and negative numbers

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] splitting positive and negative numbers from 2 column to 2 columns

[SOLVED] Find consecutive 15 positive or negative numbers in a column

Convert negative to positive in sheet containing both positive/negative numbers

Gathering the sum of negative numbers & positive numbers in a column

[SOLVED] How do I change a column of 500 numbers from positive to negative

How to turn positive numbers in a column to negative numbers ?

[SOLVED] Any way to convert column of numbers from positive to negative?

Bookmarks

Bookmarks

Posting Permissions