Returning numeric nulls with nested offset, match, index

[merryberry]

Can this be done - make a numeric* variable return null (blank) and not zero? Am relying on a seperate column to flag numeric nulls in my numeric data, to deal with later. I've tried all sorts of work rounds in cell format custom types, and using ifblank always returns a text value into my numeric column. This is not mission critical, but oh boy it is bugging me.


*I've sorted out issues with text values in a different column which are null, that was easy.


Thank you for your time and ideas in advance.

 

[Hui]

MerryBerry


Firstly, Welcome to the Chandoo.org forums.


Can you tell us what your trying to do rather than asking for a specific solution?

or better still is to post a sample file, Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

If we understand what your trying to do there may be better or alternative ways to tackle the issue

 

[asa]

Hi MerryBerry,

A couple unclear points from your question:

1) worksheets don't really have variables

2) the closest thing to a null on a worksheet is a totally blank cell; not even containing a formula, or the cell error value

#NULL! which is very rare (I don't think it's returned by any built-in Excel functions or formulae).


Regarding totally blank cells  --  a formula that refers to a blank cell will return either a blank text value or the number 0 depending on context.  By default that will be the number 0.  If appended to another piece of text, it will be treated as an empty text string ("").


For example, you might find this simple illustration helpful, depending on what you are trying to do:

[pre][code]_  Col A    Col B                   Col B (result)
1  Apple    =IF(ISBLANK(A1),"",A1)  Apple
2       34  =IF(ISBLANK(A2),"",A2)              34
3           =IF(ISBLANK(A3),"",A3)
4  Banana   =IF(ISBLANK(A4),"",A4)  Banana
If you just used a simple cell reference in column B, you'd get:

[pre][code]_  Col A    Col B                   Col B (result)
1  Apple    =A1                     Apple
2       34  =A2                                 34
3           =A3                                  0
4  Banana   =A4                     Banana
If you want to treat a formula resulting in "" that appears blank, or a blank text value entered with a ' character or some other method as a blank as well as cells with nothing in them, check for an empty string ("") instead of using ISBLANK:

[pre]_  Col A    Col B                   Col B (result)
1  Apple    =IF(A1="","",A1)        Apple
2       34  =IF(A2="","",A2)                    34
3           =IF(A3="","",A3)
4  Banana   =IF(A4="","",A4)        Banana
5  '        =IF(A5="","",A5)

[/pre]
If you want a column of TRUE/FALSE boolean values where TRUE indicates the cell is entirely blank OR contains an empty string of text, this:

_ Col A Col B Col B (result)
1 Apple =A1="" FALSE
2 34 =A2="" FALSE
3 =A3="" TRUE
4 Banana =A4="" FALSE
5 ' =A5="" TRUE[/code][/pre]
For TRUE/FALSE strictly indicating totally blank empty cells:

_ Col A Col B Col B (result)
1 Apple =ISBLANK(A1) FALSE
2 34 =ISBLANK(A2) FALSE
3 =ISBLANK(A3) TRUE
4 Banana =ISBLANK(A4) FALSE
5 ' =ISBLANK(A5) FALSE[/code][/pre]
Even if these tips help, you may want to explain your overall task better and Hui and others may be able to suggest other approaches.


Asa

 

[NARAYANK991]

Hi Asa ,


Welcome back ! Missed your posts all these months.


Narayan

 

[asa]

Thanks Narayan!

This is a great community... I'm not doing a lot with Excel right now.. I hope to be around more in the future.


Wish you the best and I'll pop in from time to time

Asa