[Solved] How to Blank Out Unwanted Results

Discuss the spreadsheet application

[Solved] How to Blank Out Unwanted Results

Postby KitchM » Mon Dec 03, 2018 9:59 pm

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 19 times
Last edited by KitchM on Tue Dec 04, 2018 4:02 am, edited 1 time in total.
KitchM
 
Posts: 46
Joined: Sun Apr 04, 2010 1:27 am

Re: How to Blank Out Unwanted Results

Postby RusselB » Tue Dec 04, 2018 1:30 am

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
Code: Select all   Expand viewCollapse view
=if(A2="";"";A2-A1)
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.
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.
User avatar
RusselB
Moderator
 
Posts: 6277
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: How to Blank Out Unwanted Results

Postby KitchM » Tue Dec 04, 2018 4:02 am

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. :bravo:
KitchM
 
Posts: 46
Joined: Sun Apr 04, 2010 1:27 am

Re: How to Blank Out Unwanted Results [Solved]

Postby RusselB » Tue Dec 04, 2018 4:17 am

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.
User avatar
RusselB
Moderator
 
Posts: 6277
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: [Solved] How to Blank Out Unwanted Results

Postby KitchM » Tue Dec 04, 2018 5:01 pm

Thank you.
KitchM
 
Posts: 46
Joined: Sun Apr 04, 2010 1:27 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 18 guests