Short answer:
If you got here from Google because you're editting a spreadsheet and suddenly you're getting `#VALUE`

, try entering the cell and pressing `Ctrl+Alt+Enter`

Long answer, expanding on maybeWeCouldStealAVan's point:

If the range is in a single column (as this is) Excel chooses the cell from that column in the same row as the referring cell (or the error #VALUE! if the range doesn't intersect that row)

This behaviour is different depending on whether or not the formula is an Array Formula*

In below image in C3 I have `=A5:A7`

, which is an error as there's no value in that range that is in row 3 (the referencing cell).

In D3 I have the same `=A5:A7`

,but I have pressed Ctrl+Shift+Enter. This is just Excel trying to convert an Array to a single cell and is not particularly useful:

Where this becomes powerful is if you want to apply a function on each row, e.g this product and sum operation:

* the discovery of which is the product of 1 hour of painful debugging, so hopefully I've saved someone some pain...

`=F3:F11`

? Is it perchance in row 4 or row 8?`#VALUE!`

error. I think @fixer1234 is reporting the same result. Please look very carefully at your Excel worksheet, and see whether it really contains what you are saying it contains. Also, this shouldn't matter (since the use of colon to specify ranges has been unchanged in Excel for many years), but please tell us what version of Excel you're using (just in case you've found some obscure bug, so we can try to reproduce it).