Calculate amt due

[OCM]

Greetings,

Given my sample workbook in Sheet1, I would like to calculate the amount due by subtracting the total collection amt from one of the settlement #1 per CustID. If the settlement #1 is blank, (e.g., customer #6 – Customer #8, then use settlement #2 to perform the calculation.

Amount due = Settlement #1 - sum of collection amt. If the amount is not entered in settlement #1, then use settlement #2 – sum of collection amt. If the amount is $0.00, then display “no amount due”



On sheet2, I’d like to show the result (attached).



Any feedback?



TIA

 

[gbaughma]

Yeah... my feedback would be "We don't do homework."

 

[combo]

With a layout as in the workbook, IMHO the only way is to manually arrange output.

If it is possible to rearrange data, I would use:

column	example/remarks
CustID	C004200
Name	Cusomer #4
SettlementID	#1, #2, ...
Type	Settlement, collection
Amount	use negative in one of types (for proper sums)
Date	add start date for Settlement

After this a simple pivot table can aggregate required values up-to-date.
More flexible analysis could combine customer+settlement data in one table and related collection in the second one. In this case pivot table could return report from tables connected in Power Query.