Page 1 of 1
[Solved] How to Blank Out Unwanted Results
Posted: 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
Posted: 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
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
Posted: 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.
Re: How to Blank Out Unwanted Results [Solved]
Posted: 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
Posted: Tue Dec 04, 2018 5:01 pm
by KitchM
Thank you.