Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Question:

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

Reply
Question marked as Solved
Answer:
Answer:

Hi Carlos,


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


Regards,

Barry

Posted on

Question marked as Helpful

Dec 15, 2017 6:23 PM in response to Carlos da Roza 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.

There’s more to the conversation

Read all replies
Question marked as Helpful

Dec 15, 2017 6:23 PM in response to Carlos da Roza 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.

Dec 15, 2017 6:23 PM

Reply Helpful (1)

Dec 21, 2017 3:53 PM in response to stfflspl 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.

Dec 21, 2017 3:53 PM

Reply Helpful

Dec 22, 2017 2:45 AM in response to Carlos da Roza 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.

Dec 22, 2017 2:45 AM

Reply Helpful

Dec 23, 2017 12:55 PM in response to Yellowbox 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




User uploaded file

Dec 23, 2017 12:55 PM

Reply Helpful

Dec 23, 2017 1:13 PM in response to Carlos da Roza 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.

Dec 23, 2017 1:13 PM

Reply Helpful

Dec 23, 2017 11:35 PM in response to Carlos da Roza 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)

User uploaded file

User uploaded file

User uploaded file

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.

Dec 23, 2017 11:35 PM

Reply Helpful
User profile for user: Carlos da Roza

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