Retun last value for a specific reference

I am running a sheet for invoicing.  We are held retention on contracts and I want to be able to lookup the final retention so that I can reference the total retention held.  This will not be the total, but the final value.  For example “Contract 1” – Should return a value of £0.00 as the retention is no longer Held, where “Contract 2” £75.00 as only 2.5% retention.

 

The calculations in the fields will be done manually, however with upto 80 contracts running and upto 50 applications this formula would be most useful.

 

 

A B C D
Contract Reference
Reference
Value Works Retention Held Net Value
1 1000 50 950
2 1000 50 950
3 1000 50 950
1 1500 75 1425
2 1500 75 1425
3 1500 75 1425
2 2000 100 1900
2 2500 125 2375
2 3000 75 2925
3 3000 150 2850
1 1500 0 1500

 

By: matt

Leave a Reply

Your email address will not be published. Required fields are marked *