Reconciling positives and negatives using formulae

[jillybeansUK]

I'm trying to create a website that performs a true up for Microsoft products. What I have are a number of licences at different version for a number of products and I need to reconciile these to installs to find the actual position. Without getting into the symantics of licences we need to assume that a later licence can cover any earlier version and I want to subtract "spares" to cover "deficits". A version used to cover must be higher than the deficit, and where possible I need to use the lowest version possible to cover a deficit, but ultimately I have to buy the latest version.

So in the attached example by manual process (in grey):

Project : I would use spares from 2013 are used to cover deficits from 2010 and 2007, but 2003 is used to cover 2002 and 2000 (leaving it as -10). 2013 is now used to cover the -10 deficit on 2003 leaving the total licence position as being deficit 5 x 2013. I ignore the 10 spare

Visio (Counted separately from Project) : I use 2013 to cancel out 2010 deficit (as I have to buy latest version), I take no action on 2007 as the licenes equal the installs, I cannot use the 100 spares in 2003

I'm essentially rolling it up from the bottom up for each product, with the added complication that any positive cannot be taken up - only the negatives (see the visio example for why I can't do a sum total), I need to be left with a single negative at the top and any remaining "spare" positive numbers. I'm happy to add extra columns (but not too many!) and total rows etc

I'd like to find a formula which can help me with this, if anyone has any suggestions?

Thanks in advance!

Licence reconciliation.xlsx

[ChemistB]

Try this arrayed formula (use CNTRL SHFT ENTER) in G2

=SUM(E2:INDEX($E$2:$E$8,MATCH(0,IF(E2:E8<0,E2:E8))))

In G11, it will be
=SUM(E11:INDEX($E$11:$E$14,MATCH(0,IF(E11:E14<0,E11:E14))))
Will that work for all scenarios?