Excel query on a cash flow sheet 2

Microsoft Office excel 2007 home & stude...
August 16, 2010 at 15:05:48
Specs: Macintosh
If the problem below is too tricky, then I have the following additional query -

(If the sum of Q1 = O1 (showing as a negative figure) + I1 (a zero amount or a positive figure) = a negative figure, I would like Q1 to show as zero, until O1 becomes a positive figure)
O1 already has conditional formatting in it.

I have the following headings on a spreadsheet that is to follow cash flow -
Column F is the rate
Column I is 25% deposit of the rate in F
Column N is total extras
Column O is Balance amount
Column P is date balance received
Column Q is total revenue
I 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 2

Report •

#1
August 16, 2010 at 16:08:39
I haven't had a chance to work on this, but after a quick reading, can't you just add a couple of columns for:

Total Rev. Due which would be the negative amounts,
and
Total Rev. Received which would be the positive amounts?

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?

You'll have to expand on this a bit.

MIKE

http://www.skeptic.com/


Report •

#2
August 16, 2010 at 16:18:09
I haven't had a chance to work on this, but after a quick reading, can't you just add a couple of columns for:
Total Rev. Due which would be the negative amounts,
and
Total Rev. Received which would be the positive amounts?

Possibly, but I was hoping it wasn't necessary to do this.
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?

This is tricky for me to explain and will try and come up with a better explanation. However I think this "(If the sum of Q1 = O1 (showing as a negative figure) + I1 (a zero amount or a positive figure) = a negative figure, I would like Q1 to show as zero, until O1 becomes a positive figure)
O1 already has conditional formatting in it." should hopefully give a solution.
Also, the conditional formatting, for some of the sheets, ie the making it red, doesn't appear to work uniformily. As in it works when you highlight a number of cells correctly, then on a number of other cells it only does it for the first one?


Report •

#3
August 16, 2010 at 16:31:51
Let me see if this question is easier to answer.
Is it possible to make a sum that if Q=I+O but only adding O if there is a figure in P?

Report •

Related Solutions

#4
August 16, 2010 at 17:14:03
Is it possible to make a sum that if Q=I+O but only adding O if there is a figure in P?

=IF(P1="",I1,I1+O1)

the conditional formatting, for some of the sheets, ie the making it red, doesn't appear to work uniformily.

It should.
How are you applying the Conditional Formatting?
The formula relies on the fact that the P cells are blank,
check to make sure there are no stray spaces or special characters in the cells.

You could also try using the reverse logic of the formula:

=IF(P2<>"",F2-I2+N2,-ABS(F2-I2+N2))

MIKE

http://www.skeptic.com/


Report •

#5
August 16, 2010 at 17:54:18
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.

What is the formula your using to arrive at the total revenue in column Q ?

MIKE

http://www.skeptic.com/


Report •

#6
August 18, 2010 at 02:18:11
Hi,

The formula currently to arrive at total revenue is Column I (deposit amount 25%) + Column O (Balance amount)

As I say, we have the following columns -

Column F - Agreed rate (Just a figure)
Column I - Deposit 25% (No sum, just 25% of agreed rate)
Column N - Extras cost (=K1*L1, K being number of people, L being cost)
Column O - Balance amount (=IF(P1="",-ABS(F1-I1+N1),F1-I1+N1)
Column P - Date balance received (Either the date or NA or CASH)
Column Q - Total revenue (=I1+O1)

Basically the way it is working at the moment, is the balance amount is negative until a date is in place, but the total revenue can also appear negative as it takes into account any deposit taken and adds the balance amount (Which could be negative if a date hasn't been added)
This then gives a false running turnover total for the year.
What we would like is for either there to be a formula to stop this, or as I mention above, allowing Q=I+O but only adding O if there is a figure in P?

Hope that makes sense,

Regards, Ian


Report •

#7
August 18, 2010 at 08:37:14
I believe this will do what you need:

In column Q beginning at Q3 use the formula:

=IF(P3="",SUM(I3+Q2),SUM(O3+Q2))

In Q2 use the formula:

=IF(P2="",I2,O2)

If that is not what your looking for, post an example of the spreadsheet with example amounts.

MIKE

http://www.skeptic.com/


Report •

Ask Question