[Solved] Copying a formula across cells ignoring blank ones

[DarrenGold]

Hi folks!

I've tried searching for this, but I'm not sure if I'm even asking the right question. Essentially I have a simple formula in a results cell (A3), that minuses the number in cell A2 from cell A1 ("=A1-A2"). Stupidly easy, obviously. But there isn't always a number in cell A2, so if I copy this formula down the results column (by dragging the little handle thingy at the bottom right of the formula/results cell), where there is no number in cell A2, the result in cell A3 is 0. That's correct, of course, but what I'd prefer is to have nothing entered there in that case. It would just make scanning down/reading the results column much easier.

Is that possible? Am I even making sense?

Cheers,

Darren.

[robleyd]

Is that possible? Am I even making sense?

Yes, and yes.

Simply use the IF() function to determine whether the result of your formula is non-zero; if so display the result, otherwise display nothing.

Code: Select all

=IF(A1-A2<>0; A1-A2; "")

There is also a global option to suppress display of zero values - Tools | Options | OpenOffice | Calc | View and uncheck Zero Values under Display. But this will affect all your spreadsheets and may not be what you want.

[DarrenGold]

Hi

Thanks for your speedy reply! And that's great! The only thing is I'm still getting a value in the results column, regardless of whether there's a calculation to be done. I didn't explain myself properly in the original query either... I should have said that the results were to be seen in cell C1 (not A3). And the calculation should have been "=A1-B1" (sorry, I posted late last night, and wasn't thinking clearly!). Not that it should matter from which cells the results derive the calculation, or where the results are displayed, but I've included a grab with this reply which shows what I'm really after, and I didn't want to muddy the waters any further.

Anyway, the grab shows that when using the formula you supplied, I don't get a '0' in C where there is no B value, but I am getting the value from A, plonked into C. What I'd really like is when there is no B value, then nothing is placed in the respective cell for that C column, i.e. the cells indicated by the arrows should be blank.

Cheers!

[robleyd]

If we can be assured that B is empty, and not the result of a formula, you could use ISBLANK() with IF()

Does your original problem of A-B=0 also exist? And is there a possibility that A will be empty and B has a value, or B also is empty, and if so what should happen in those cases?

[DarrenGold]

Hi

Yes, some cells in B will always be empty. And no, A-B=0 won't exist in this case/for this sheet... nor will A ever be empty (or A and B empty together).

As for using ISBLANK(), how would the entire formula look? I can't figure out where it could be placed within =IF(A1-A2<>0; A1-A2; "")

[robleyd]

Given you are using columns A and B to store your values, and placing the result of A-B in C, then this formula in C will work and give a blank C if B is empty .

Code: Select all

=IF(ISBLANK(B1);"";A1-B1)

You have said the A-B will never be zero so it does not take that possibility into account.

If you did want to suppress zeros also, the formula would be:

Code: Select all

=IF(OR(ISBLANK(B1);A1-B1=0);"";A1-B1)

Both tested with dummy data.

[DarrenGold]

Hi David

That's perfect (I can use either). Thank you so much!

Cheers,

Darren.