2

I am learning excel functions and modeling from Google and trial and errors so just want to clarify the following point to get my fundamental understanding correct:

When it comes to colon in excel, I understand that it means defining a range of cells, but when I enter =F3:F11 excel gives the result 30. May I know why is that so? I was trying to interpret it using average / mode etc. and evaluated the formula but fail to find any logical meaning behind it.

enter image description here

5
  • 1
    Into what cell are you putting the formula =F3:F11? Is it perchance in row 4 or row 8?
    – hBy2Py
    Apr 29, 2015 at 3:27
  • sorry my mistake i have made correction to that. i tried to add printscreen of my excel sheet but cant without enough reputation. the formula i stated is as what i entered but excel did not return error result. it returns 30 instead. u can try that with the data stated input and typing in the same formula. the cell i put the formula is just a random cell i chooses.
    – unacorn
    Apr 29, 2015 at 5:16
  • "u can try that with the data stated input and typing in the same formula."  I just tried exactly that, and I got a #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). Apr 29, 2015 at 5:45
  • To add a screenshot, put the image somewhere publicly accessible and add the link to your question. Someone with the rep will amend your question for you and embed the image. Apr 29, 2015 at 8:09
  • @fixer1234 sorry this is the first time i am using this website so i don't really get used to how many things work here yet. thanks for informing me :) I did not use google sheets, that was the result i've gotten from microsoft excel 2010. That is why i was confused by the use of colon. would it be any chance that's caused by bugs?
    – unacorn
    Apr 30, 2015 at 3:22

2 Answers 2

8

F3:F11 is a range, but you're entering it in a place where it's only appropriate to give a single value, so Excel tries to choose one value from the range, using the following rules:

  • 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)
  • If the range is in a single row, Excel chooses the cell from that row in the same column as the referring cell (or the error #VALUE! if the range doesn't intersect that column)
  • If the range is 2-dimensional, Excel chooses the cell from the same row and column as the referring cell (or the error #VALUE! if the range doesn't intersect the range rows and columns) - clearly this only works when the range and calling cell are on different worksheets

Caution:

  • If the range reference is given where a range or array is appropriate, the entire range will be used -- so, in cell M4, =F3:F11+1 would be 31, but =sum(F3:F11,1) would be 331.
  • If entered as an array formula (using ctrl+shift+enter) in the same single cell, the formula will return the entire array but you'll only see one cell, since that's all that fits in the result range. The result will be 10. Presumably Google's ARRAYFORMULA works the same way.

When is this useful?

In general I don't like to use this behavior when referring to a cell in the same table - an ordinary relative reference (F4) works just as well, and I don't need to worry about how my formulas will interpret the reference. (See cautionary statement above.)

One use for this, though, is when you want one worksheet to line up one-to-one with another. I can bring the columns I want over in a column reference (eg =Sheet1!$A:$A) and let the other columns be calculated fields. I could do this with a relative reference as well, (eg =$A1 and drag down) but there are advantages to referencing the column - I can insert, delete, or sort rows in the source sheet without breaking the references. (With single-cell references I'd get, on insert, a row missing from the referring data; on delete, a #REF! error; on sort, the two sheets would no longer be in the same order.)

5
  • I've used this behavior in Excel versions 2007, 2010, and 2013. I'm using 2010 on this computer. May 4, 2015 at 9:34
  • Strange! I replicated the example when it was first posted and got #VALUE!. I just went back and did it again and now I'm getting the same results as the OP. It didn't work until you explained it. Guess my Excel needed to hear the explanation from you to know what to do. :-) OK, obviously you're right. +1
    – fixer1234
    May 4, 2015 at 9:44
  • It's an odd behavior - usually you can enter a formula (in A1 format) from any cell to test the result, but not this one. May 4, 2015 at 9:58
  • That's what's strange. I previously replicated the example exactly as shown and got #VALUE!. The only difference is that you hadn't yet explained to Excel how it was supposed to work. That's my story and I'm sticking to it. :-)
    – fixer1234
    May 4, 2015 at 10:19
  • Thank you so much for the clear explanation @maybeWeCouldStealAVan that is certainly a well structured answer beyond explaining the reasoning behind it (and love the information of when is it useful to use this reference :))
    – unacorn
    May 6, 2015 at 1:08
0

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:

array ref example

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

product and sum

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.