[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 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
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: 5601
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 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
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: 5601
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: dimos19, Google [Bot] and 18 guests