Excel help - again

Gers

I'd be grateful for some advice about an Excel worksheet please.

I've created a very basic worksheet to record simple money transactions:



Purely for 'looks' I'd prefer that the running totals in columns C and D don't show all the way down on the empty rows - is that possible without removing the copied formulas?

thanks

JGB1955

What formula have you put in C3 and D3. And how are you going to show cash in?

Neil_Jones

You can wrap it in an IF statement, something like this:

=IF(J10<0.9,SUM(I10*J10*$A$1),)

This particular example I've copied from a spreadsheet of my own but the basic principle here is check the value of a cell, if its below a certain value then do a sum, otherwise return a blank cell. You should be able to change this using the ISBLANK function again in an IF statement.

WaywardDriver

Formula in C2 =IF(ISBLANK(B2),"",C1+B2).

Heedtheadvice

Plus if if you want to show the summation based on the contents of more than one cell then combine the formula with logical operators such as

C2 =IF( or( not(ISBLANK(A2) ), NOT( ISBLANK(B2)) ) ,C1+B2, "")


i.e. if cell A2 or cell B2 have an entry (not blank) then sum otherwise make cell empty.


Note a possilble downfall is if the cells to be tested are not empty but have text in them then they are not blank, a summation will be attempted and return an error! That error can be trapped by including ISNUMBER() function in the cell code or probably better still using that instead of isblank !

WaywardDriver

Heedtheadvice wrote: »

Note a possilble downfall is if the cells to be tested are not empty but have text in them then they are not blank, a summation will be attempted and return an error! That error can be trapped by including ISNUMBER() function in the cell code or probably better still using that instead of isblank !

Good thinking, formula =IF(ISNUMBER(B2),C1+B2,"")