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

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

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?

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?

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

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

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

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

Ask Your Question

Weekly Poll

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History