Hi all
OK, so here is my scenario:
I have a holding of shares in a share portfolio, broken down by individual parcels per holding (for cost base purposes). For example, I have ANZ shares with multiple parcels (due to various purchase dates) along with various different cost bases. I then need to maintain a running balance for this holding, taking in to account disposals, and where there is a disposal, take it from the lowest cost base with a remaining balance.
My question is; is there a formula that essentially says: if there is a sale, deduct it from the lowest cost base parcel?
Currently, my formula is =IF(AND(MATCH("Sale",$C:$C,0)>0,G8>0,(INDEX($G:$G,MATCH("Sale",$C:$C,0))>G8),K8=MIN(K:K)),G8,0)
However, the above formula only takes the lowest cost base, and doesn't take the remaining balance of the sale into account. So the formula I need, needs to be able to apply the whole sale balance to the lowest available cost bases.
It may not be possible, and I may not have explained myself too well, but any help would be greatly appreciated.
I have attached an extract of the file so you can see what I'm trying to do.
Please let me know if I can offer any further clarification to the ^.
Cheers
Tim
OK, so here is my scenario:
I have a holding of shares in a share portfolio, broken down by individual parcels per holding (for cost base purposes). For example, I have ANZ shares with multiple parcels (due to various purchase dates) along with various different cost bases. I then need to maintain a running balance for this holding, taking in to account disposals, and where there is a disposal, take it from the lowest cost base with a remaining balance.
My question is; is there a formula that essentially says: if there is a sale, deduct it from the lowest cost base parcel?
Currently, my formula is =IF(AND(MATCH("Sale",$C:$C,0)>0,G8>0,(INDEX($G:$G,MATCH("Sale",$C:$C,0))>G8),K8=MIN(K:K)),G8,0)
However, the above formula only takes the lowest cost base, and doesn't take the remaining balance of the sale into account. So the formula I need, needs to be able to apply the whole sale balance to the lowest available cost bases.
It may not be possible, and I may not have explained myself too well, but any help would be greatly appreciated.
I have attached an extract of the file so you can see what I'm trying to do.
Please let me know if I can offer any further clarification to the ^.
Cheers
Tim