# 1. consecutive days of profit and of losses 2. biggest profit loss since date

1. ## 1. consecutive days of profit and of losses 2. biggest profit loss since date

Hi community,

I'm am new here. I am working for days on a larger spreadsheet. Answers to the following two question could boost the analysis and I am currently doing with the dataset and let me save a lot of time.

A simple spreadsheet that lists dates, profits/looses

A1=Date; B1=Profit/Loss
A2-A100=Weekly Dates; B2-B100=Profit/Loss Data

1. I would like to count consecutive days of profit and of losses = e.g. the output should display 3 if the last 3 consecutive weeks a profit was realised (10 if the last consecutive 10 weeks the Profit/Loss number was positive. If after the three weeks (10 weeks) the Profit/Loss data is negative the output should jump to zero and start counting from 0 again.
Negative weeks should also be counted as above = output 3 after 3 three consecutive negative figures (or alternatively: -3 after three consecutive negative numbers), jumping to zero when a profit turns up.

Could some help me with this task?

2. Second output I would like to display: the last profit/loss figure represents the biggest profit/loss since date
-> highest profit number since "according date needed here (wished output) and the profit on that date". In case of a loss: highest loss (lowest number) since "according date needed here and the loss on that date". The date is more important to me.

Example:

Date - Profit - Output I would like to display: Highest/Lowest number since
1.28.2000 - 20 - 1.7.2000 25
1.21.2000 - -5
1.14.2000 - 17
1.7.2000 - 25
1.1.2000 - 4

Any ideas? Thanks a lot in advance, I am thankful for any advices or ideas.
Best Regards from Berlin,
xbohemianx

2. ## Re: 1. consecutive days of profit and of losses 2. biggest profit loss since date

1. in C2 put in 0 (since with the first entry, the trend is unknown)
in C3 put in:
=IF(SIGN(B2)=SIGN(B3),C2+1,0)
This is what you asked for, but I think you really don't want it to reset to 0, since it makes the second week of a positive/negative run show up as 1. I think you want the 'reset' to be a 1 instead of a 0 since it conveys a 1 week run of positive/negative. If you agree, then change the 0 in the equation above to a 1.

2. Not entirely sure how you decide when to output the data. Every week will have a 'highest profit/loss since <date>', even if it is just the week before. In other words you would see something like this:
Jan 500 Highest profit since Jan
Feb 600 Highest profit since Jan
Mar 700 Highest profit since Feb
Apr 400 Highest profit since Apr
May 450 Highest profit since Apr
Jun 800 Highest profit since Mar
Jul 800 Highest profit since ??? {is this Jun or Jul?}

So, in essence, just about every line would have a statement. Maybe you could ignore the ones that are calling out the same month, but you would still get a lot of output.

Or, using your data, why don't you have this for 1.14.2000:?
1.14.2000 - 17 - 1.7.2000 25

The rules aren't defined enough for me to work with.

3. ## Re: 1. consecutive days of profit and of losses 2. biggest profit loss since date

Originally Posted by Pauleyb
1. in C2 put in 0 (since with the first entry, the trend is unknown)
in C3 put in:
=IF(SIGN(B2)=SIGN(B3),C2+1,0)
This is what you asked for, but I think you really don't want it to reset to 0, since it makes the second week of a positive/negative run show up as 1. I think you want the 'reset' to be a 1 instead of a 0 since it conveys a 1 week run of positive/negative. If you agree, then change the 0 in the equation above to a 1.

2. Not entirely sure how you decide when to output the data. Every week will have a 'highest profit/loss since <date>', even if it is just the week before. In other words you would see something like this:
Jan 500 Highest profit since Jan
Feb 600 Highest profit since Jan
Mar 700 Highest profit since Feb
Apr 400 Highest profit since Apr
May 450 Highest profit since Apr
Jun 800 Highest profit since Mar
Jul 800 Highest profit since ??? {is this Jun or Jul?}

So, in essence, just about every line would have a statement. Maybe you could ignore the ones that are calling out the same month, but you would still get a lot of output.

Or, using your data, why don't you have this for 1.14.2000:?
1.14.2000 - 17 - 1.7.2000 25

The rules aren't defined enough for me to work with.
Hi Pauleyb,

thanks a lot for your solution for the first task. I tried a few versions yesterday and your thought represents the easiest solution. Thanks a lot! I still included a "helper" column that shows me: was the consecutive series a profit or loss run and simplifies pulling automated texts from the output. Your are of course perfectly right, with the value jumping back to 1 instead of 0.

consecutive runs.JPG

Regards the task to display the date of the "latest higher/lower number" in the data column I created the attached spreadsheet to explain the task.

save.jpg

You are right the output could be displayed over the whole column. I would only be interested in the ouput of the new data that is inserted, so I only wrote it down once in my example yesterday.

So 4 outputs are possible either a new high or low is created with new data (Output: Highest profit; lowest profit) or the data is within the max.-min. range of the old data (here I would like to new when the latest higher profit or equal profit was realised, or when the latest lower loss or equal öpss was realised).
The screenshot or the spreadsheet carry further information.

Thanks again for your help, allready solely the solution for the first question is of great help to me!
Regards
bohemian

4. ## Re: 1. consecutive days of profit and of losses 2. biggest profit loss since date

Maybe the use of a Pivot Table like this would help.

5. ## Re: 1. consecutive days of profit and of losses 2. biggest profit loss since date

Originally Posted by newdoverman
Maybe the use of a Pivot Table like this would help.
Hi newdoverman,

thanks for having a look at it and for your suggestion. I am afraid a pivot would not fit my needs though, it would create a better overview and allows a quick manual check for these numbers.

I found something that helps and someone might have an idea how to reach the next step.
The following formula is a good step into the direction I would like the output to look like:

=IF(ROW(B2)=2,"",IF(B2>MAX(\$B\$1:B1), "this value is
the highest since " & TEXT(INDEX(\$A\$1:A1,MATCH(MAX(
\$B\$1:B1),\$B\$1:B1,0)), "mmmm yyyy"), IF(B2<MIN(\$B\$1:B1),
"this value is the lowest since " & TEXT(INDEX(\$A\$1:A1,
MATCH(MIN(\$B\$1:B1),\$B\$1:B1,0)), "mmmm yyyy"),"")))

______________________________

I am glad for this output - but it only gives me the "highest since" or "lowest since" for data that is stricking out the caputured data range. So only data new highs and lows are recognized as such.

Please have a look at this screenshot or the attached spreadsheet. I receive the note: highest value/lowest value was reached but no information if the value is within the previous range.

highest.jpg

To give you an every day example: in the financial paper you can often read the price of an asset has reachest the highest/lowest price since then, e.g.: S&P500 reached highest level since - MMDDYYYY.
Now if an asset has reached a signifikant high and a significant low over time and from then on trading only within that previous range -> the output of the above formula would not be interesting as no new highs or lows are reached. So receiving also information about the price movement within that range would allow comments like the stock X reached highest level since MMDDYYYY (also if it is "no all time high" within the dataset). That is exactly what I need the output to do. Highlight of new record numbers in the data set is good - but I need this further information.

If anyone could help me that would mean a lot to me. Any ideas also if they only represent a partial solution are welcome.

Attached you can find the spreadsheet again with the success of getting highest and lowest values as output.

Thanks for the possibilty of sending in questions here in the forum and the quick replies of you newdoverman & Pauleyb!

Greetings from Berlin!

6. ## Re: 1. consecutive days of profit and of losses 2. biggest profit loss since date

This formula works for your example:
=IF(B2>0,IF(B2=MAX(\$B\$2:B2),"Highest Profit",INDEX(\$A\$2:A2,MATCH(MAX(\$B\$2:B2),\$B\$2:B2,0))),IF(B2=MIN(\$B\$2:B2), "Highest Loss",INDEX(\$A\$2:A2, MATCH(MIN(\$B\$2:B2),\$B\$2:B2,0))))

However, I think it does not work in a case you did not illustrate. What should it output if you change the value in cell B7 from a -30 to a 2? I believe the intent would be for it to show 1/29, but it shows 1/22. That case is where it gets tricky and would require more thought.

BTW, you don't cover the case of a Profit/Loss of 0 for your column D. If it not possible to get a 0, then my initial equation could be simplified to something like IF(B2*B3>0,C2+1,1), but I chose to use SIGN since I assume there is a chance of 0 profit.

7. ## Re: 1. consecutive days of profit and of losses 2. biggest profit loss since date

This seems to match my expectations, maybe not yours though (see my comments above):
In cell G2:
``Please Login or Register  to view this content.``
In cell G3:
``Please Login or Register  to view this content.``
use ctrl-shift-enter to enter the formula, then drag it down to G10.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1