I'm trying to use the correlation function in analysis toolpak but I keep getting "INPUT RANGE CONTAINS NON-NUMERIC DATA". I've used the =SUMPRODUCT(--NOT(ISNUMBER(range))) function for the columns I want to check, and all columns I want to use have turned up 0 but unfortunately I am still getting "INPUT RANGE CONTAINS NON-NUMERIC DATA". Ideas?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
INPUT RANGE CONTAINS NON-NUMERIC DATA but all columns show no existing non-numeric data
- Thread starter winds
- Start date
Zero Length Characters? How do you check for that?
Upvote
0
awoohaw
Well-known Member
- Joined
- Mar 23, 2022
- Messages
- 5,096
- Office Version
- 365
- Platform
- Windows
- Web
I'm not sure what your data is like. But there are ASCII characters that have zero length. They do show up in LEN() function. If you have using data that was copied from a web page that can happen. Carriage Returns are also an example. Here is the list of characters. to see the code of a character use the CODE() function, it takes only 1 character, ie. CODE("A") = 65, to select a character from a code use the CHAR() function, CHAR(65) = "A". There are some you tube lessons and probably some threads in this help forum on how to find and remove them. Here is an example of char 28-31 being in a text string that isn've visible but affects the length: (Ignore the "SEQUENCE FUNCTION" , im just using it to create the text strings for the example. You can get a list of the characters with this: Char(Row(A1:A255)), if your version of excel can do spilled formulas.
| WorkBook1.xlsx | |||||||
|---|---|---|---|---|---|---|---|
| D | E | F | G | H | |||
| 64 | DISPLAY | LENGTH | REMOVED | LENGTH REMOVED | |||
| 65 | CHAR(28): | JOHN1234567890 | 15 | JOHN1234567890 | 14 | ||
| 66 | CHAR(29): | JOHN1234567890 | 15 | JOHN1234567890 | 14 | ||
| 67 | CHAR(30): | JOHN1234567890 | 15 | JOHN1234567890 | 14 | ||
| 68 | CHAR(31): | JOHN1234567890 | 15 | JOHN1234567890 | 14 | ||
Sheet1 | |||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D65:D68 | D65 | ="CHAR("& SEQUENCE(4,1,28,1) &"):" & CHAR(SEQUENCE(4,1,28,1)) |
| E65:E68 | E65 | ="JOHN" & CHAR(SEQUENCE(4,1,28,1)) & "1234567890" |
| F65:F68,H65:H68 | F65 | =LEN(E65) |
| G65 | G65 | =REPLACE(E65,FIND(CHAR(28),E65),1,"") |
| G66 | G66 | =REPLACE(E66,FIND(CHAR(29),E66),1,"") |
| G67 | G67 | =REPLACE(E67,FIND(CHAR(30),E67),1,"") |
| G68 | G68 | =REPLACE(E68,FIND(CHAR(31),E68),1,"") |
| Dynamic array formulas. | ||
Upvote
0
awoohaw
Well-known Member
- Joined
- Mar 23, 2022
- Messages
- 5,096
- Office Version
- 365
- Platform
- Windows
- Web
you can also see if values in cells are numbers by removing any alignment formatting. Also, if you are using some aggregate or array function that resolve to TRUE/FALSE, you can wrap that part of the expression in parentheses and multiply by "--" or 1* or 0+ and that will force the expression to be a number before the ISNUMBER test.
Upvote
0
| 40 | 72.5 | 36 | 63.89 | 32 | 93.75 | 25 | 76 | 29 | 23 | 79.31 | 21 | 18 | 86 | 34 | 22 | 64.71 | 2.7 | 15 | 2022 | 1 | 3 | 3 | 3 | 4 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 4 | 3 | 3 | 4 | 4 | 4 | 4 | 4 | 4 | 3 | 3 | 3 | 4 | 4 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 4 | 4 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 4 | 3 | 3 | 3 | 4 | 4 | 3 | 3 | 3 | 4 | 4 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 4 | 3 | 4 | 3 | 3 | 3 | 3 | 3 | 3 | 34 | 14 | 16 | 3 | 0 | 1 | 14 | 41 | 30 | 88 | 33 | 97 | 33 | 97 | 1 | 3 | 34 | 12 | 16 | 2 | 2 | 2 | 12 | 35 | 28 | 82 | 30 | 88 | 32 | 94 | 2 | 6 | 34 | 1 | 7 | 15 | 7 | 4 | 1 | 3 | 8 | 24 | 23 | 68 | 30 | 88 | 4 | 12 | 34 | 2 | 18 | 7 | 3 | 4 | 2 | 6 | 20 | 59 | 27 | 79 | 30 | 88 | 4 | 12 | 34 | 24 | 8 | 1 | 0 | 1 | 24 | 71 | 32 | 94 | 33 | 97 | 33 | 97 | 1 | 3 |
| 51 | 49.02 | 55 | 40 | 45 | 51.11 | 35 | 65.71 | 54 | 30 | 55.56 | 45 | 28 | 62.22 | 42 | 15 | 35.71 | 2.5 | 11 | 2022 | 1 | 4 | 4 | 3 | 4 | 3 | 4 | 3 | 3 | 3 | 3 | 4 | 3 | 3 | 4 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 4 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 4 | 3 | 4 | 3 | 3 | 3 | 3 | 3 | 3 | 2 | 3 | 2 | 4 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 2 | 3 | 3 | 3 | 3 | 3 | 3 | 42 | 7 | 17 | 12 | 1 | 5 | 7 | 17 | 24 | 57 | 36 | 86 | 37 | 88 | 5 | 12 | 42 | 12 | 10 | 8 | 6 | 6 | 12 | 29 | 22 | 52 | 30 | 71 | 36 | 86 | 6 | 14 | 42 | 1 | 6 | 8 | 13 | 14 | 1 | 2 | 7 | 17 | 15 | 36 | 28 | 67 | 14 | 33 | 42 | 5 | 15 | 6 | 8 | 8 | 5 | 12 | 20 | 48 | 26 | 62 | 34 | 81 | 8 | 19 | 42 | 11 | 23 | 2 | 3 | 3 | 11 | 26 | 34 | 81 | 36 | 86 | 39 | 93 | 3 | 7 |
| 12 | 83.33 | 11 | 90.91 | 9 | 77.78 | 5 | 60 | 9 | 8 | 88.89 | 13 | 13 | 100 | 13 | 9 | 69.23 | 3.2 | 9 | 2022 | 1 | 3 | 2 | 3 | 4 | 3 | 3 | 3 | 3 | 3 | 4 | 3 | 3 | 3 | 3 | 2 | 2 | 2 | 3 | 3 | 3 | 3 | 2 | 2 | 2 | 2 | 2 | 2 | 3 | 2 | 2 | 2 | 1 | 3 | 3 | 2 | 3 | 3 | 3 | 3 | 3 | 3 | 4 | 4 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 4 | 2 | 2 | 2 | 1 | 2 | 2 | 2 | 3 | 3 | 3 | 2 | 3 | 2 | 3 | 3 | 3 | 3 | 4 | 2 | 2 | 2 | 2 | 3 | 2 | 3 | 2 | 3 | 2 | 13 | 9 | 4 | 0 | 0 | 0 | 9 | 69 | 13 | 100 | 13 | 100 | 13 | 100 | 0 | 0 | 13 | 10 | 1 | 2 | 0 | 0 | 10 | 77 | 11 | 85 | 13 | 100 | 13 | 100 | 0 | 0 | 13 | 3 | 4 | 2 | 2 | 2 | 3 | 23 | 7 | 54 | 9 | 69 | 11 | 85 | 2 | 15 | 13 | 6 | 6 | 1 | 0 | 0 | 6 | 46 | 12 | 92 | 13 | 100 | 13 | 100 | 0 | 0 | 13 | 8 | 5 | 0 | 0 | 0 | 8 | 62 | 13 | 100 | 13 | 100 | 13 | 100 | 0 | 0 |
Just to give an example, this is part of the data for one row. Although some of the data in other rows had some blanks, and some had '-', NA, N/A but I've replaced all of these with 0 and still getting the error.
Upvote
0
awoohaw
Well-known Member
- Joined
- Mar 23, 2022
- Messages
- 5,096
- Office Version
- 365
- Platform
- Windows
- Web
well. Is this raw data or calculated fields? I'm guessing they are calculated fields. If just raw data you could take that into the power query/power pivot and clean it up there.
Also, if you get xl2bb you can post a snippet of the range with the errors and the formula cell and folks can try to debug with the real data.
Also, if you get xl2bb you can post a snippet of the range with the errors and the formula cell and folks can try to debug with the real data.
Upvote
0
awoohaw
Well-known Member
- Joined
- Mar 23, 2022
- Messages
- 5,096
- Office Version
- 365
- Platform
- Windows
- Web
winds, did you paste one row or 3 rows into the message? When I posted I got a very strange result. The Column A pasted 4072.53663.893293.752576292379.31211886342264.712.71520221333433333333343344444433344333333333333333333443333333333433344333443333333434333333. into one cell then the remaining cells of that row were put into a column. Then it did the same for the 2nd and 3rd row.
What is Column A?
What is Column A?
Upvote
0
It's supposed to be one very long row but for some reason it pasted as three rows. I'm not sure if it's spilled arrays
Upvote
0
Similar threads
- Solved
- Question
- Question
