MS Excel: IS.EMPTY ?

[FlightDetent]

I seem to have troubles understanding this:

http://1drv.ms/1KVEe9H

Why do I get different results when ISEMPTY argument references the same range?

Thanks for any advice, FD.

[ExGrunt]

Hi FD,

Normally, I like to try and help people with Excel queries, but in this case your post looks like an invite to a driveby malware site so I am not going to click the link.

I suspect this may be why no one else has bothered to reply. I suggest that you re-post your question giving a fuller description of the problem in your post - then we will try to help.

HTH

EG

[Bushfiva]

It's his OneDrive account URL.

[FlightDetent]

Thanks, I see your point and need to agree fully.


I tried to create a rather simple table, intending that the cells that have =SUM() display nothing if the adressed range has no input values. For this I chose the ISBLANK function. But the results I got puzzle me quite a lot.


The link is indeed to my OneDrive, I used the MS shortener, this is the long one: https://onedrive.live.com/redir?resid=917C1722E4A3421!302&authkey=!AJj7xJ_bmTRAenU&ithint=file%2cxlsx


pic: http://i57.tinypic.com/16i78qq.png


at H4, formula =ISBLANK(B3:B5) returns TRUE but at H6 =ISBLANK(B3:B5) returns FALSE. I cannot figure how to make it work.

My versions of Excel, both the full app and the online tool, are localized to Czech language. I suppose people with EN installation will see the formulas in English though.





FD.

[G0ULI]

Try changing the formula in cell H4 to read =IS.BLANK($B$3:$B$5)

Then copy and paste the formula to cell H6

By inserting the dollar signs you force Excel to use the cells defined in the formula rather than recalculating them to B5:B7 which is what happens if you just cut and paste the original formula without the dollar signs.

The results for H4 and H6 should then read exactly the same.

[ExGrunt]

HI FD,

Thank you.

Can I tease out exactly what you want as a result because in Excel there is a difference between zero, null/blank and #N/A.

One of the problems with your approach is that ISBLANK sometimes only looks at the first cell in a range and does not check the subsequent cells.

If you want to check for blank/null values rather than zero or #N/A then this should work in cell B6:

If all the cells in B3:B5 MUST have values:
IF(COUNTBLANK(B3:B5)>0,"",SUM(B3:B5))

HTH

EG

[FlightDetent]

Thanks gents.

I question why are the results not the same?
[H4]=ISBLANK(B3:B5) >> true
[H6]=ISBLANK(B3:B5) >> false

As far as I am convinced, the returned value should be TRUE in H4 as well as H6.

The plan was to sum points on a score card. The score values are integers <0;10>, additional possible characters are "-", "x" or an empty cell. To make the form look neat, I desire not to see "0" as the sum of the column until at least one of the scores is entered. My solution was this: =IF(ISBLANK(B3:B5);"";SUM(B3:B5)). Plain language: if the score column is completely empty, show no character, otherwise show the sum of score column. But I did not get what I intended so started troubleshooting. The above is where I got stuck.

[bnt]

I think we have a bug here. I just tried the following:

- open Excel (2016 preview) from scratch;
- in E1, enter formula =ISBLANK($B$3:$B$5)
- copy the formula from E1 down and across over range E1:L10

Because I locked the references, the formula is exactly the same in all cells E1:L10, but the results in rows 3, 4, 5 are TRUE, the rest are FALSE.
At no point did I even look at cells B3:B5, never mind put anything in them!

edit: I thought it might help to enter the formula as an array formula (https://support.office.com/en-gb/article/Guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7), but that didn't work properly either. It looks to me as if ISBLANK just doesn't work on a range.

This works: =(COUNTBLANK($B$3:$B$5)=3) is TRUE if all 3 cells are blank, FALSE if one or more has something in it. Putting that in to your formula, we have =IF(COUNTBLANK(B3:B5)=3,"",SUM(B3:B5))

PPS: Excel actually has an option to suppress zero values. In recent versions it's under File / Options, Advanced section. "Show a zero in cells that have zero value" is normally checked, uncheck it to suppress zeros.

[Capot]

I can't follow the problem or the discussion so far, but just in case it's relevant I find that using @IF functions is useful; for example to extract positive values from a long list of negative and positive values, and then get a total of positive values, I use @IF(d4>0,d4,0), which creates a column of positive values and zeros, so that Autosum works for the total. The zeros can be hidden.

@IF can be used for all sorts of analyses; a text string works just as well as a number within the argument. For example, we use it to extract addresses with a particular post code from a database. When combined with a macro it can be very powerful.

[ExGrunt]

Hi Capot,

The @functions in excel are included to allow a degree of compatibility with Lotus 1-2-3. There is no guarantee that they will be retained or that they will function consistently. However, your post did remind me that cell formatting may be a better and cleaner way forward.

Excel custom formatting (select cell - right click - select format - click number - select custom) has the following structure:

positive number format;negative number format;zero format;text format

eg:
* #,##0;-* #,##0;"";@

will display a blank cell for any zero value without having to use any formulae.

HTH

EG

[darkroomsource]

ISBLANK does not support a range
https://support.office.com/en-us/article/ISBLANK-function-983fc90d-3897-43b5-97cc-db36de15fd06

The "microsoft" solution is here:
Need formula to determine whether a range is blank - Microsoft Community (http://answers.microsoft.com/en-us/office/forum/office_2007-excel/need-formula-to-determine-whether-a-range-is-blank/81045ed8-a1cb-43a3-b5e0-9d1a16c09769)

(although ISBLANK does not support a range, it does allow it, and this, probably, should not be allowed)