Page 1 of 1

[Solved] How to Blank Out Unwanted Results

PostPosted: Mon Dec 03, 2018 9:59 pm
by KitchM
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?

Re: How to Blank Out Unwanted Results

PostPosted: Tue Dec 04, 2018 1:30 am
by RusselB
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.

Re: How to Blank Out Unwanted Results

PostPosted: Tue Dec 04, 2018 4:02 am
by KitchM
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:

Re: How to Blank Out Unwanted Results [Solved]

PostPosted: Tue Dec 04, 2018 4:17 am
by RusselB
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

Re: [Solved] How to Blank Out Unwanted Results

PostPosted: Tue Dec 04, 2018 5:01 pm
by KitchM
Thank you.