[Solved] How to Blank Out Unwanted Results
[Solved] How to Blank Out Unwanted Results
In the attached sample spreadsheet file (which is a portion of another sheet), Row 7 has a problem in that it creates unwanted numbers. I would prefer that Row 7 remains all blank until data is entered into Columns A, C and H. How can I accomplish that?
- Attachments
-
- Sample Spreadsheet.ods
- Sample File
- (10.17 KiB) Downloaded 116 times
Last edited by KitchM on Tue Dec 04, 2018 4:02 am, edited 1 time in total.
Re: How to Blank Out Unwanted Results
I'm going to suggest wrapping some of the formulas in an IF statement. Specifically the formulas in columns B, D, and I.
In column B, I'd suggest using This is a simpler method of calculating the number of days, which is what the DAYS function does, and it takes into account if the current row has an entry in column A or not. You can enter the above in B2 and then copy it down for however many rows you feel are needed.
You can also copy that same formula over to column D and column I, as the reference points will adjust automatically.
This will handle most of the calculations I suspect that you were wondering about... others will become a 0 or updated as the required information is entered.
You do have two columns with static data entered, which I have not touched, though looking at the data makes me think that these could be changed to a formula so that the previous entry is used, rather than a static entry on all rows... by changing them to formulas, they could be wrapped in an IF function similar to the one shown above.
In column B, I'd suggest using
Code: Select all
=if(A2="";"";A2-A1)
You can also copy that same formula over to column D and column I, as the reference points will adjust automatically.
This will handle most of the calculations I suspect that you were wondering about... others will become a 0 or updated as the required information is entered.
You do have two columns with static data entered, which I have not touched, though looking at the data makes me think that these could be changed to a formula so that the previous entry is used, rather than a static entry on all rows... by changing them to formulas, they could be wrapped in an IF function similar to the one shown above.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Re: How to Blank Out Unwanted Results
Thanks, RusselB. That looks pretty simple and elegant. If I understand what is going on, the formula states that IF something is in A then do the subtraction. In that case, the THEN is automatic when nothing is there and B is therefore left blank.
Thanks very much. That saves the day for me.
Thanks very much. That saves the day for me.
Re: How to Blank Out Unwanted Results [Solved]
Specifically it checks if the cell is blank, and if it is returns a null character, the closest we can get to returning a blank in a formula, otherwise it does the math
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.