2

Ok, I've been looking for this type of formula but not finding it. So here I am asking for help.

I'm a day trader on stocks and I want to analyze my performance. For example:

+-----------------+--------------+-----------+-----------------+----------+
|      Days       | Profit/Loss  |  Per Day  | Per Day Pr/Loss | Best Day |
+-----------------+--------------+-----------+-----------------+----------+
| Monday          | $100         | Monday    |           -1088 | Friday   |
| Tuesday         | -$50         | Tuesday   |           -3550 |          |
| Thursday        | $21          | Wednesday |            -958 |          |
| Monday          | $12          | Thursday  |              21 |          |
| Monday          | -$1200       | Friday    |            5715 |          |
| Wednesday       | -$1200       |           |                 |          |
| Friday          | $3200        |           |                 |          |
| Tuesday         | -$3500       |           |                 |          |
| Wednesday       | -$300        |           |                 |          |
| Wednesday       | $42          |           |                 |          |
| Wednesday       | $500         |           |                 |          |
| Friday          | $15          |           |                 |          |
| Friday          | $2500        |           |                 |          |
+-----------------+--------------+-----------+-----------------+----------+

P/L according to days should be:

Mondays -$1088,  
Tuesdays -$3550,  
Wednesdays -$958,  
Thursdays $21,  
Fridays $5715 

(How can I calculate this as well?)

And the most profitable days traded from the example above should be FRIDAY.

Now my question is, how can I formulate this so that it gives me Friday as the answer?

0

1 Answer 1

2

You need to use the QUERY and VLOOKUP functions:

In cell D2 place the following formula nad drag it down to cell D6

=QUERY($A$2:$C,"select sum(B) where A='"&$C2&"' label sum(B)''")

In cell D2 place the following:

=Vlookup(max($D$2:$D),{$D$2:$D,$C$2:$C},2,FALSE)
3
  • Please check the updated answer. Dec 25, 2019 at 18:41
  • Wow. Thanks for the quick response and the edit.
    – AnakManis
    Dec 25, 2019 at 20:38
  • Have you tried the solution given? Did it work for you? If not we should try finding out why. If yes, as per site guidelines when an answer addresses your question, you should accept it and even upvote it so others can benefit as well. Feb 21, 2020 at 20:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.