# Excel query on a cash flow sheet

Microsoft Office excel 2007 home & stude...
August 16, 2010 at 14:55:10
Specs: Macintosh
 I have the following headings on a spreadsheet that is to follow cash flow - Column F is the rateColumn I is 25% deposit of the rate in FColumn N is total extrasColumn O is Balance amountColumn P is date balance receivedColumn Q is total revenueI firstly asked the question of how to include a formula so that Column O gave a negative amount if a date wasn't put in column P.The problem I now have is the total revenue is taking these negative figures into account and not giving a true total revenue running reading.The second problem is the total revenue is taking into account the negative balance amount in column O, even if a deposit has been paid?Is there a formula to rectify this problem?Any help greatly received, or a further explanation can be given,Regards, Ian

See More: Excel query on a cash flow sheet

#1
August 17, 2010 at 05:04:01
 Hi,Column Q on each row can use this formula to show zero if no balance paid or to show the balance paid once the column P cell (date balance received) has been completed:=IF(P2="",0,O2)To total column O (balance amount) for all rows use this:=SUMIF(O2:O10,">0",O2:O10)(adjust row numbers as appropriate)This only sums positive amounts.To find total of outstanding balances use this:=SUMIF(O2:O10,"<0",O2:O10)This only sums negative amounts(I am assuming that the amount in column O changes from negative to positive when column P is completed).Regards

Report •
Related Solutions