Excel Formula

Question

Good evening, I have a tricky one. Lets see if I can explain simply as I can't post this financial data.

I have this formula in cell C33 too add up and continue a tally of the bank balance at the end of every month. Eventually I will do for every month following Row 33. The formula looks like this.

=IF(COUNT(C31)=1,SUM(B33+C31),"")

Cell C31 is a end figure of expenses and income for the month (as in a figure to tell me if I am at a loss or not).

Cell B33 is the bank balance of the previous month.

I am trying to stop this formula to add up until I have completed the monthly tallies. I.e if there is no figure in Cell C31 yet as the month has not been tallied, then I want the cell to remain empty until these figures have been applied.

Here is the catch. It works if I just type a number manually into cell C31, However this has a formula which basically minuses the expenses total off the income total (both taken from another cell) in order to gain the figure lost or gained. Why would the formula in cell C31 be stopping this from happening? Is there another way to achieve the end goal?

Attached is a link to the Spreadsheet in question. I have taken out other sheets inside the book for privacy reasons. Nothing links back to the summary page anyway. Figures also been doctored for obvious reason.

MICROSOFT HELP.xlsx

Answer 1

COUNT(C31) is TRUE if C31 is numeric. C31 contains a formula that ALWAYS returns a number, so COUNT(C31) is always TRUE. You might use

=IF(C31<>0,B33+C31,"")

(SUM is superfluous since you already use +)

Thanks for your assistance. Bit of a rookie when it comes to excel. I have managed to get it working. Really appreciate the help.

Do you mind explaining your formula? I noticed the count function is missing? Is this not required? Am I right in saying that your formula is stating if cell C31 is equal to zero it will not complete the formula of B33+C31? Just trying to learn a bit whilst Im at it.

One last thing. Is there a way that when the formula doesn't add things up that the cell can have the dollar sign and - in it like the other cells?

If at all possible

$           -

Answer 2

Correct yes. I go through at the end of each month and tally up the income and expenses for that month. Sorry my explanation skills suck as I havent done this in a long long time. Bit of a rookie when it comes to this sort of thing. I am trying to understand your formula. Do you mind explaining each part? I presume the IF(AND) statement is different to the IF(COUNT) I had before? How does it differ?

thanks for such a prompt reply, much appreciated.

Answer 3

I am trying to stop this formula to add up until I have completed the monthly tallies.

What is that supposed to mean? Only carry out the calculation when income and expenses arise?

C33: =IF(AND(C12<>0,C31<>0),B33+C31,0)

Andreas.

Answer 4

COUNT(C31) is TRUE if C31 is numeric. C31 contains a formula that ALWAYS returns a number, so COUNT(C31) is always TRUE. You might use

=IF(C31<>0,B33+C31,"")

(SUM is superfluous since you already use +)