All I need to do is compare debit and credit columns from client TB to debit and credit columns from our AP software to generate columns 5 and 6 which are the adjustments for the client - you know the sort of thing.
Having got on my high horse recently and told our team off for using Excel as a WP tool instead of using formulae, I find that I'm struggling with this one! I suspect nested functions are involved, but my brain is too small to cope. The problem is that a debit could arise from an increasing debit, a decreasing credit or a credit becoming a debit!
Can anyone help?
Chris Martin
Replies (2)
Please login or register to join the discussion.
use if statements
Suggest you create a 5th colum, which can then be hidden, which works out the differance between the TB and your version. then your adjustment columns work on:
If(column5>0, " ",column 5)for your debit adjustment, and
if (column5<0," ", -column5)for your credit adjustment
How about MIN & MAX functions
with 0 as one of the options
So
=MIN(0,ColA-ColB) will give 0 if A>B or A-B if not.
and
=MAX(0,ColA-ColB) will give A-B if A>B or 0 if not.