Placing a null (i.e. no value) into a cell

Question

Placing a null (i.e. no value) into a cell

Does anyone know of a way to place a null (i.e. no value) in a cell such as:

IFERROR(<<test-value>>,<<null>>)

A great number of the statistical functions, such as CORREL and STDEV exclude a cell in its calculations if it is empty, but obviously not when it is zero, and I want to take a measure on a partially empty list.

Actually, there are a number of times I would like to leave the cell empty after a test, but do not know how to assert it. I do not want to put in an empty string (""), zero, or and error condition. I thought perhaps a function BLANK might do the trick, but there is no such function, just a test for blank (ISBLANK).

MacBook Pro (Retina, 13-inch, Late 2013), macOS Sierra (10.12.6)

Posted on Dec 14, 2017 8:10 PM

Reply

Answer 1

Dec 23, 2017 12:55 PM in response to Yellowbox

Here's a cut and paste of the little 5x5. The description and formulas can be seen in the accompanying screen grab. I'm still trying to figure out the exact conditions, and it seems that the SLOPE and UNION.RANGES together are a problem.

FALSE	1	10	4	16
	2	12	5	18
	3	14
SLOPE+ UNION.RANGES
SLOPE	2

Reply

Answer 2

Yellowbox

Level 7

20,383 points

Dec 23, 2017 11:35 PM in response to Carlos da Roza

Hi Carlos,

To stop you tearing your hair out, I get the same result (A1 is a checkbox as a quick way to insert TRUE or FALSE)

I don't know how "." got into the second argument of SLOPE

Perhaps it is best to start a new discussion.

Focussing on union.ranges, rather than this title (null value) will attract more helpers.

Regards,

Ian.

Reply

Answer 3

stfflspl

Level 3

575 points

Dec 15, 2017 6:23 PM in response to Carlos da Roza

Carlos,

the CORREL function ignores strings (like ""):

CORREL(y-values, x-values)

y-values: The collection containing the y (dependent) values. Each value can be a number value, date/time value, or duration value. All values must be of the same value type.

x-values: The collection containing the x (independent) values. Each value can be a number value, date/time value, or duration value. All values must be of the same value type.

Notes

Both collections must have the same dimensions.
If string values or boolean values are included in the collections, they are ignored.

On the other hand, STDEVA signs a zero to strings.

Maybe, UNION.RANGES might help you, if it is feasible to select non-empty cells only.

Paul.

Reply

Answer 4

Dec 21, 2017 3:53 PM in response to stfflspl

UNION.RANGES does concatenate collections nicely, and CORREL does ignore "".

I thought maybe I was out of the woods, but ran into SLOPE (I am trying to do some regression plotting against a 2D-scatterplot where some of the values need to be conditionally nulled out - e.g. an entry like IFERROR(a1/b2,"") where B2 can be zero or empty). Unlike CORREL, SLOPE doesn't like "". I got around that by running columns and columns of intermediate calculations, but it sure would be cleaner and easier to just use SLOPE and INTERCEPT.

Reply

Answer 5

Yellowbox

Level 7

20,383 points

Dec 22, 2017 2:45 AM in response to Carlos da Roza

Hi Carlos,

To help us help you, please post a screen shot of your data. Delete or hide personal information.

Or, if your table is large, you can select the cells and copy. Paste into a reply. We can copy and paste into a Numbers table. That will save us having to retype 🙂.

Regards,

Ian.

Reply

Answer 6

Dec 23, 2017 1:13 PM in response to Carlos da Roza

This is very much simplified to illustrate the issue. The actual application is for much larger tables of biometrics (e.g. blood pressure, pulse, etc.) where certain measurements have to be rejected, others omitted, and linear regression applied to the normalized values. It can actually be done by performing the intermediate calculations for the underlying formulas, but table sizes grow dramatically as trends are taken against each new test variable.

Reply

Answer 7

Barry

Level 9

59,861 points

Dec 15, 2017 6:22 PM in response to Carlos da Roza

Hi Carlos,

IFERROR(formula,"") will insert a null string ( "" ), a text value with zero length.

Regards,

Barry

Reply

Answer 8

Dec 15, 2017 6:25 PM in response to Carlos da Roza

Thanks, Barry, Paul. It solves my issue.

Reply