Spreadsheet: 721060d1614446057-help-proof-a-formula-with-sumproduct-and-average-average-days-held-sample-2
Sheet: Sheet2

 |  |  |  | Table 1
 |  |  |  |  | Status | Symb. | Qty | Days Held |  |  |  |  |  |  |  | Status | Symb. | Qty | Days Held
 |  |  |  |  | Closed | NLY | 5 | 10 |  | NLY |  | <<< Formula A |  |  |  | Closed | NLY | 5 | 43 |  |  |  | NLY |  | <<< Formula A
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Closed | NLY | 5 | 36
 |  |  |  |  | Closed | NLY | 1 | 5 |  | NLY |  | <<< Formula B |  |  |  | Open | AGNC | 5 | 26 |  |  |  | NLY |  | <<< Formula B
 |  |  |  |  | Closed | NLY | 1 | 5 |  |  |  |  |  |  |  | Closed | NLY | 5 | 134
 |  |  |  |  | Closed | NLY | 1 | 5 |  |  |  |  |  |  |  | Closed | NLY | 5 | 208
 |  |  |  |  | Closed | NLY | 1 | 5 |  |  |  |  |  |  |  | Closed | NLY | 5 | 50
 |  |  |  |  | Closed | NLY | 1 | 5 |  | NLY |  |  |  |  |  |  |  |  |  |  |  | In my original template There are Multiple Symbols, Qty, Status and Days.
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Closed | NLY | 5 | 78 |  |  | I Need to Calculate The average Days these multiple Positions were Held
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Closed | AGNC | 5 | 130
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | I am Not Sure If [NAME] is Correct. I know [NAME] is incorrect
 |  |  |  | Table 2 |  |  |  |  |  |  |  |  |  |  |  | Closed | NLY | 2 | 204 |  |  | Can Someone Proof Formula A ?
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | And If [NAME] is [NAME], What would be the Correct Formula?
 |  |  |  |  | Status | Symb. | Qty | Days Held |  |  |  |  |  |  |  | Open | NLY | 2 | 204
 |  |  |  |  | Closed | NLY | 5 | 10 |  | NLY |  | <<< Formula A |  |  |  | Closed | NLY | 1 | 204 |  |  | Thank you:)
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Closed | AGNC | 5 | 130
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Closed | AGNC | 3 | 125
 |  |  |  |  | Closed | NLY | 5 | 5 |  | NLY |  | <<< Formula B |  |  |  | Closed | NLY | 2 | 125
 |  |  |  |  | Closed | NLY | 0 | 0 |  |  |  |  |  |  |  | Open | NLY | 5 | 32
 |  |  |  |  | Closed | NLY | 0 | 0 |  |  |  |  |  |  |  | Closed | NLY | 1 | 86
 |  |  |  |  | Closed | NLY | 0 | 0
 |  |  |  |  | Closed | NLY | 0 | 0 |  | NLY |  | <<<Formula C |  |  |  | Open | NLY | 1 | 160
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Closed | AGNC | 1 | 58
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Closed | NLY | 10 | 15
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Closed | NLY | 10 | 88
 |  |  |  | If I Hold 10 Units  for 10 days, And 5 Single Units for 5  Days |  |  |  |  |  |  |  |  |  |  |  | Closed | NLY | 5 | 86
 |  |  |  | How many days is the average holding days per each unit?
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Open | NLY | 9 | 0
 |  |  |  | I think that [NAME] is Correct. But when calculating a Large Database I am a bit doubtful |  |  |  |  |  |  |  |  |  |  |  | Closed | NLY | 10 | 71
 |  |  |  | That’s why I was wondering if [NAME] is indeed the correct method of calculating
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Closed | AGNC | 4 | 0
 |  |  |  | To simplify the problem I have created Two Small Tables.  Table 1 And Table 2 |  |  |  |  |  |  |  |  |  |  |  | Closed | NLY | 4 | 0
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Closed | NLY | 2 | 0
 |  |  |  | I think [NAME] is the correct result because |  |  |  |  |  |  |  |  |  |  |  | Open | AGNC | 5 | 0
 |  |  |  | if you holding something 10 days and somethingelse 5 days the average holding would be 7.5 days |  |  |  |  |  |  |  |  |  |  |  | Closed | AGNC | 1 | 0
 |  |  |  | for each and all the units And Not 5.8333 days |  |  |  |  |  |  |  |  |  |  |  | Closed | AGNC | 10 | 0
 |  |  |  | As you can see in Table 1 and Table 2, Both have the same QTY and Days Held,
 |  |  |  | The difference is HOW the data is input
 |  |  |  | Sometimesthe QTY is executed trouought the day in separate lots, in my case would be Table 1
 |  |  |  | And Table 2 would be Summerised
 |  |  |  | Now in Table 2 Delete the data in the Highlighted area and see what happends to the results
 |  |  |  | Formula A does Not change, Unlike the other formulas.
 |  |  |  | Thank you:)