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
Bookmarks