My speadsheet keeps track of when patients leave a specific floor of the hospital. There are columns in which where each patient goes is documented (1 column for "home", another column for "rehab facility", another for "other floor", etc.). Only when the patient leaves the hospital altogether does it count as a discharge, in which case the “discharge” column needs to have something in it.

What formula can I use to generate, say, an "x" in the "discharge" column if certain "where they went" columns in the same row contain something, but not if there is nothing in any of them?

Currently, to accomplish this I am using =IF(OR(M3>0,N3>0,O3>0,P3>0,Q3>0,R3>0,S3>0),"x") in row 3 of the "discharge" column (rows 1 and 2 are headings), and I have used "fill down" in all subsequent rows. To suppress the "FALSE" this formula yields when the condition is not true, I have applied conditonal formatting to the entire column that if the value=FALSE then the font is white (same colour as background).

Is there a more efficient, elegant and idiot-proof way of doing this? The conditional formatting of text colour could potentially confuse everyone but the person who built the spreadsheet (me).


You could use =IF(SUM(M3:S3)>0,1,0) and use custom number formatting "X";"Err";"" to display the resulting 1 or 0 as an "X" or blank respectively.


all you need to do to make them blank is use:

IF(some conditions,"x","")

Your IF is missing a second result to tell it what to display if FALSE. Double-double-quotes returns a blank cel (although note that it is not blank when it comes to things like COUNTA as it has something in - a formula).

Mike's IF statement using a SUM is more elegant than your clunky OR as long as the cells will have numbers (or dates or times, which are just fancy numbers). I answered separately rather than commenting since the key to your problem is not the condition but the fact you have not specified "value_if_false".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy