Excel shenanigans

I have been involved with a great stack of data over the last few months; building a massive formula that so far covers all the possible falsey input I can think of!

I’ve edited all cell references so it’s obvious where they lie; and not what they refer to. Personally I find cognitive search-and-replace really frustrating, and Excel has never made debugging input to its formula bar easy (I can only imagine the frustration a command-line PHP dev’ must have!)…

The magic started life as:

=IF(OR(I6="",K6=""),"?",NETWORKDAYS(I6,K6,))

Designed around NETWORKDAYS to figure out the amount of time between two user-defined dates; it makes a nominal effort to filter out bad data.

Obviously, not all input is blank (as in the example above) but I’m getting to a work-around later…

The new version is:

 =IF(OR(I6="",K6="",IFERROR(SEARCH("~?",I6,1),"FALSE"),IFERROR(SEARCH("~?",K6,1),"FALSE")),"?",IFERROR(NETWORKDAYS(I6,K6,),"?"))

This took a great deal longer to build, and yes: makes use of only two other functions: =IFERROR() and =SEARCH().

Riffing off the text replacement (“?”) in version 1; I decided to search for Ye Olde question mark. Handily, I had also been using them to blank out dates (remember this is for calendar-related data) in the spreadsheet in question. Therefore I know I won’t get muchas “ERROR!” returns.

However, now there can be a question-mark anywhere inside a given piece of text, and that field is treated the same (i.e. it’s got some duff data in it, or none at all!).

I realise that the =IFERROR() around =NETWORKDAYS() is a little unnecessary; but it covers all the bases. True, you still ensure get the obligatory q-mark for your efforts, but then only if you screw up!

True: there doesn’t have to be old Q as a mark; but as mentioned there could be unknowns inside the set. Yes: not all my data is certain. Definitely part of the reason this is in the first place…

Thanks to these handy, if badly presented, routines; I can ensure some UX design has been put in place. Yes: I think this comes under experience; yes: other people than me read this spreadsheet.Yes: therefore comments, or some sort of feedback, would be important to provide in a succinct manner.

True: as is stated in CommNet.edu: “Use a question mark at the end of a direct question”. Also true: there isn’t a question explicitly stated; but I feel this implies a gap in the set.

Job done? Suggestions are of course welcome! I’ll probably be pimping it in the future…

Advertisements

Tags: , , , , , , , , , ,

About Nick

Professional bureaucrat, ex-KUSECWB, graduate & techie-monsta / computer-wrangler; at your service.
%d bloggers like this: