help needed in worksheet function

[Guest]

Hi to all good people,

I am workin on an excel sheet in which I have to make Maping of Payment
received and mode of payment is Cheque and Cash. I am in collection business
and have to map daily payment received that i am doing by vlookup but when a
cheque gets dishonoured i have to deduct the same amount from payment
received is there any way to do it easily. I am giving details of my excel
worksheet

column A2 to Z2 i have detail of customer
column AA2 having data of all payment received
column AB2 to BE2 colums i am having data of payment received on daily and
sum of all dates comes in column AA2 here so i can get total figure of
payment collected in whole month.
My problem is i am maping payment collection on a daily basis but when a
cheque payment made by the customer can be return after two days so i have to
delete the amount from collection i want to know that how can i do it by
formulas. column AB2 to BE2 are column of collection received on respected
date ie. 1-March, 2-March, .........
can i place any formula to remove collected amount if cheque returns
dishonoured.

i tried harder but not able to find the answer suggest me if there is any
way can ease my problem.

Hope u good people will do. Thanking u in advance,

Aarif

 

[prabhuraaman]

You could create a new column for cheques dishonoured from BE2 and
could detect the amount form the total amount
Thus if your dishonoured columns runs from BF2 to BM2 then your formula
in AA2 would be
=SUM(AB2:BE2)-SUM(BF2:BM2)

You could maintain a pivot table to store your data in a systematic
format.Learn more from Microsoft Excel help