Hello,
I am trying to find several metrics on my spreadsheet (attached) but the duplicate rows in column A and multiple criteria needed are giving me problems.
1. For average time difference between column C and column B, I have 2 formulas (L2 and L3) but they don't allow for blank cells. If there are, it results in #DIV/0!. I would like this formula to work with blank cells as well, by taking average difference between each column, and ignoring blank cells because I plan to add more entries as time goes on.
2. Is there a formula that calculates the total of each number in column E based on its respective item in column A, not counting duplicates in column A. Again I'll need this formula to ignore blank cells for future entries. For this example, I'll need the formula to calculate the total amount of Long trades only. The answer is in M6.
3. Is there a formula for finding the average of column G per each column A group. For example, if column A has duplicates, I need the average of the respective rows in column G, then to add that average to the next group of averages and so on. The average is based on 8 trades, not 15. Answer in M11
4. Is there also a formula to calculate the average of of column G based on column A group and column H. So for each duplicate or group in column A, it calculates the average in column G if its respective row in column H says "Win", then add averages together for a total average. Answer in M12.
5. Lastly, is there a formula to find the largest value in column D based on column A, but if there are duplicates, the respective rows in column D must be added together, then the formula find which value in that column is the largest. Answer in M14
I've tried different variations of SUMPRODUCT and COUNTIF but keep getting errors, #DIV/0! or 0. If some of the formulas are the same or similar when providing an answer, you can let me know and I'll figure it out. Any help is much appreciated!
Bookmarks