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.
[Solved] Copying a formula across cells ignoring blank ones
-
- Posts: 6
- Joined: Mon May 17, 2021 7:19 pm
[Solved] Copying a formula across cells ignoring blank ones
Last edited by robleyd on Wed Jun 02, 2021 2:35 am, edited 1 time in total.
Reason: Tagged [Solved]; add green tick
Reason: Tagged [Solved]; add green tick
OpenOffice 4.1.7 / Windows 10
Re: Copying a formula across cells, but ignoring blank ones?
Yes, and yes.Is that possible? Am I even making sense?
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; "")
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
-
- Posts: 6
- Joined: Mon May 17, 2021 7:19 pm
Re: Copying a formula across cells, but ignoring blank ones?
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!
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!
- Attachments
-
- sheet-grab1.png (5.55 KiB) Viewed 1387 times
OpenOffice 4.1.7 / Windows 10
Re: Copying a formula across cells, but ignoring blank ones?
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?
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?
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
-
- Posts: 6
- Joined: Mon May 17, 2021 7:19 pm
Re: Copying a formula across cells, but ignoring blank ones?
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; "")
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; "")
OpenOffice 4.1.7 / Windows 10
Re: Copying a formula across cells, but ignoring blank ones?
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 .
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:
Both tested with dummy data.
Code: Select all
=IF(ISBLANK(B1);"";A1-B1)
If you did want to suppress zeros also, the formula would be:
Code: Select all
=IF(OR(ISBLANK(B1);A1-B1=0);"";A1-B1)
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
-
- Posts: 6
- Joined: Mon May 17, 2021 7:19 pm
Re: Copying a formula across cells, but ignoring blank ones?
Hi David
That's perfect (I can use either). Thank you so much!
Cheers,
Darren.
That's perfect (I can use either). Thank you so much!
Cheers,
Darren.
OpenOffice 4.1.7 / Windows 10