I have following data:
Each Pass # have its corresponding profit/loss across currency pair. Foss # 1, AUDNZD & AUSUSD are -ve (-188 is there sum). Pass # 1 profit is 23+23+45+78+6+5+3+4=187 if we dont count -90-98 = -181. For same AUDNZD and AUDUSD pair, pass # 10 has max profit (456+7=463). I want to have a formula probably using Xlookup and SUMIF (or anything else) where is can maximize profit using two passes; one for max profit (using SUMIF(B2:K2,">0") and taking the largest value. for 2nd pass, i need to look at -ve values of currency pair of 1st pass and finding the best profit value for the same Pairs in second pass. How can i do it?
Pass # | AUDCAD | AUDCHF | AUDJPY | AUDNZD | AUDUSD | CADCHF | CADJPY | CHFJPY | EURAUD | EURCAD |
1 | 23 | 23 | 45 | -90 | -98 | 78 | 6 | 5 | 3 | 4 |
2 | 98 | 3 | 4 | 5 | 6 | -9 | -97 | 45 | 345 | 56 |
3 | -88 | 98 | 0 | 54 | 56 | -32 | 45 | -90 | 4 | 34 |
4 | -45 | 76 | -9 | -2 | -56 | 33 | 34 | -87 | 24 | 4 |
5 | -100 | 65 | -9 | -1 | 45 | -44 | 24 | -97 | 323 | 55 |
6 | -98 | 65 | -9 | -8 | 46 | -55 | -90 | 5 | 5 | 645 |
7 | 67 | -9 | -56 | 9 | 45 | 9 | -45 | -87 | 345 | -90 |
8 | 4 | -4 | -89 | 9 | 4 | 89 | -1 | 9 | 2 | 76 |
9 | 32 | -96 | 90 | 8 | 56 | -89 | -45 | -46 | 345 | -80 |
10 | 67 | 87 | -87 | 7 | 456 | 98 | -67 | -23 | 57 | -88 |
11 | 45 | 64 | -45 | 6 | 46 | 90 | -56 | -11 | 78 | 654 |
12 | 3 | 34 | 57 | 9 | 6 | -98 | -90 | -34 | -89 | 43 |
13 | 34 | 67 | 67 | 8 | 5 | 67 | -45 | -34 | 3 | 3 |
14 | 23 | 78 | 57 | 7 | 5 | 57 | 100 | -34 | 3 | 33 |
15 | -23 | -12 | -34 | -90 | -98 | 90 | 0 | -45 | 43 | -65 |
Each Pass # have its corresponding profit/loss across currency pair. Foss # 1, AUDNZD & AUSUSD are -ve (-188 is there sum). Pass # 1 profit is 23+23+45+78+6+5+3+4=187 if we dont count -90-98 = -181. For same AUDNZD and AUDUSD pair, pass # 10 has max profit (456+7=463). I want to have a formula probably using Xlookup and SUMIF (or anything else) where is can maximize profit using two passes; one for max profit (using SUMIF(B2:K2,">0") and taking the largest value. for 2nd pass, i need to look at -ve values of currency pair of 1st pass and finding the best profit value for the same Pairs in second pass. How can i do it?