Could someone please explain why I’m getting #VALUE error for SMALL formulae (column C) in this document?
Create randomised number list in groups 7-1 , return red format for entire groups (4 or less) when all are odd (col. B).ods (15.9 KB)
In your column B, the values are texts (since they are calculated using the Mid function).
You can use an extra column.
Alternatively, enter in C1 an array formula:
=SMALL(--$B$1:$B$7;1)
(At the end of typing, press Ctrl+Shift+Enter).
Or select the range C1:C7 and enter the array formula
=SMALL(--B$1:B$7;ROW($B$1:$B$7))
1 Like
That may or may not work depending on the detailed calculation settings how to convert text content to numbers.
1 Like
…or use =SMALL(VALUE(B$1:B$7);1) instead `=SMALL(B$1:B$7;1)
…or use =VALUE(MID(A1; RANDBETWEEN(1;8);1)) instead =MID(A1; RANDBETWEEN(1;8);1)
In any case, provide the SMALL() function with numeric arguments instead of text ones.
1 Like
Because all content in both columns A and B is of type text (that just looks like numbers) instead of numeric.
Use this instead:
| A | B |
|---|---|
| 12345678 | =NUMBERVALUE(MID(A1; RANDBETWEEN(1;8);1)) |
| =NUMBERVALUE(SUBSTITUTE(A1;B1;"")) | =NUMBERVALUE(MID(A2; RANDBETWEEN(1;7);1)) |
1 Like