Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
offset() function #VALUE!
1. Name="Data" B4:B11 = {"data", 1,2,3,4,5,6,7}
2. formula =OFFSET(Data,1,0,3,1) 's value is 2 when at E6 , seems fine 3. But same formula becomes an error #VALUE! when at E5 Why different result happens at E5 and E6 with same formula? I think =OFFSET(Data,1,0,3,1) has nothing to do with its own position. -- 事情不耐做,問題不耐看。 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
offset() function #VALUE!
Offset uses a single cell as the reference. When You use a range name for the
reference then Excel uses the first cell as the reference. You have set the Height of three so when the formula is copied it will come to empty cells and produce an error. Using the same data 1 to 8 in cells B4:B11 the following formula produces the sum of the next three rows. Note: The reference row is not absolute. =SUM(OFFSET($B4,0,0,3)) Not value is returned and the totals wil decrease when there is no value in the cell covered by the Height. Extend up and down and the value will eventually be zero. HTH Peter Atherton "H.C. Chen" wrote: 1. Name="Data" B4:B11 = {"data", 1,2,3,4,5,6,7} 2. formula =OFFSET(Data,1,0,3,1) 's value is 2 when at E6 , seems fine 3. But same formula becomes an error #VALUE! when at E5 Why different result happens at E5 and E6 with same formula? I think =OFFSET(Data,1,0,3,1) has nothing to do with its own position. -- 事情不耐做,問題不耐看。 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
offset() function #VALUE!
You have set the Height of three so when the formula is copied it will come
to empty cells and produce an error. But there's no empty cells in the Height of three! I make it clearer by this example : B4:B11 = {"data";11;22;33;44;55;66;77} J4 =OFFSET($B$4,1,0,3,1) = #VALUE! <-------- 11 is expected but why error? J5 =OFFSET($B$4,1,0,3,1) = 11 <--------------- Correct J6 =OFFSET($B$4,1,0,3,1) = 22 <----- I think it should be still 11, but why 22? -- 事情不耐做,問題不耐看。 "Billy Liddel" wrote: Offset uses a single cell as the reference. When You use a range name for the reference then Excel uses the first cell as the reference. You have set the Height of three so when the formula is copied it will come to empty cells and produce an error. Using the same data 1 to 8 in cells B4:B11 the following formula produces the sum of the next three rows. Note: The reference row is not absolute. =SUM(OFFSET($B4,0,0,3)) Not value is returned and the totals wil decrease when there is no value in the cell covered by the Height. Extend up and down and the value will eventually be zero. HTH Peter Atherton "H.C. Chen" wrote: 1. Name="Data" B4:B11 = {"data", 1,2,3,4,5,6,7} 2. formula =OFFSET(Data,1,0,3,1) 's value is 2 when at E6 , seems fine 3. But same formula becomes an error #VALUE! when at E5 Why different result happens at E5 and E6 with same formula? I think =OFFSET(Data,1,0,3,1) has nothing to do with its own position. -- 事情不耐做,問題不耐看。 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
offset() function #VALUE!
It's because you have the height argument set to 3 and the function returns
a 3x1 array. In cell J4, if you array enter the formula it will return the correct result. Because the formula returns an array and the other cells, J5:J6, are within the *implicit intersection* of the formulas range you get *correct* results in J5:J6. Try entering the formula in cell A10 then copy down a few cells. You'll get all results of #VALUE!. The implicit intersection is: the nth element of an array that refers to a cell in the same row/column that a normally entered array formula is entered in. Does that make sense? That might have to be read a few times to understand!!! See if this helps: ...........B..........J 4.....data........=OFFSET($B$4,1,0,3,1) 5......11.........=OFFSET($B$4,1,0,3,1) 6......22.........=OFFSET($B$4,1,0,3,1) If you normally enter the formula in J4 and copy down to J6 the *correct* results a #VALUE! 11 22 As written the formula returns a 3x1 array comprised of the range B5:B7. Since the formula wasn't array entered the formula in J4 returns the #VALUE! error. Now, here's where the implicit intersection rule comes into play. The formula in J5 is on the same row as B5 which is the first element in the height argument array returned by the formula. The formula in J6 is on the same row as B6 which is the second element in the height argument array returned by the formula. The formula in J4 refers to B5 but since the formula was not array entered and is not on the same row as B5 (not within the implicit intersection) the result is an error. For your formula to work properly you need to select a 3x1 array of cells then array enter the formula as "block array". expbiff101 -- Biff Microsoft Excel MVP "H.C. Chen" wrote in message ... You have set the Height of three so when the formula is copied it will come to empty cells and produce an error. But there's no empty cells in the Height of three! I make it clearer by this example : B4:B11 = {"data";11;22;33;44;55;66;77} J4 =OFFSET($B$4,1,0,3,1) = #VALUE! <-------- 11 is expected but why error? J5 =OFFSET($B$4,1,0,3,1) = 11 <--------------- Correct J6 =OFFSET($B$4,1,0,3,1) = 22 <----- I think it should be still 11, but why 22? -- ?????,?????? "Billy Liddel" wrote: Offset uses a single cell as the reference. When You use a range name for the reference then Excel uses the first cell as the reference. You have set the Height of three so when the formula is copied it will come to empty cells and produce an error. Using the same data 1 to 8 in cells B4:B11 the following formula produces the sum of the next three rows. Note: The reference row is not absolute. =SUM(OFFSET($B4,0,0,3)) Not value is returned and the totals wil decrease when there is no value in the cell covered by the Height. Extend up and down and the value will eventually be zero. HTH Peter Atherton "H.C. Chen" wrote: 1. Name="Data" B4:B11 = {"data", 1,2,3,4,5,6,7} 2. formula =OFFSET(Data,1,0,3,1) 's value is 2 when at E6 , seems fine 3. But same formula becomes an error #VALUE! when at E5 Why different result happens at E5 and E6 with same formula? I think =OFFSET(Data,1,0,3,1) has nothing to do with its own position. -- ?????,?????? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
offset() function #VALUE!
HC
You need to reset the offset value and use Hieght of one. When you are copying down the ROWS function is useful when a change of offset value is needed. J4: =OFFSET($B$4,ROWS($1:1),0,1,1) J5: =OFFSET($B$4,ROWS($1:2),0,1,1) J6: =OFFSET($B$4,ROWS($1:3),0,1,1) =OFFSET($B$4,ROWS($1:4),0,1,1) =OFFSET($B$4,ROWS($1:5),0,1,1) =OFFSET($B$4,ROWS($1:6),0,1,1) =OFFSET($B$4,ROWS($1:7),0,1,1) Enter the first fomula in J4 then copy down as far as required. See how the ROWS increments by one as it is copied down, it is the second value that is used. Hope this helps. Peter Atherton "H.C. Chen" wrote: You have set the Height of three so when the formula is copied it will come to empty cells and produce an error. But there's no empty cells in the Height of three! I make it clearer by this example : B4:B11 = {"data";11;22;33;44;55;66;77} J4 =OFFSET($B$4,1,0,3,1) = #VALUE! <-------- 11 is expected but why error? J5 =OFFSET($B$4,1,0,3,1) = 11 <--------------- Correct J6 =OFFSET($B$4,1,0,3,1) = 22 <----- I think it should be still 11, but why 22? -- 事情不耐做,問題不耐看。 "Billy Liddel" wrote: Offset uses a single cell as the reference. When You use a range name for the reference then Excel uses the first cell as the reference. You have set the Height of three so when the formula is copied it will come to empty cells and produce an error. Using the same data 1 to 8 in cells B4:B11 the following formula produces the sum of the next three rows. Note: The reference row is not absolute. =SUM(OFFSET($B4,0,0,3)) Not value is returned and the totals wil decrease when there is no value in the cell covered by the Height. Extend up and down and the value will eventually be zero. HTH Peter Atherton "H.C. Chen" wrote: 1. Name="Data" B4:B11 = {"data", 1,2,3,4,5,6,7} 2. formula =OFFSET(Data,1,0,3,1) 's value is 2 when at E6 , seems fine 3. But same formula becomes an error #VALUE! when at E5 Why different result happens at E5 and E6 with same formula? I think =OFFSET(Data,1,0,3,1) has nothing to do with its own position. -- 事情不耐做,問題不耐看。 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
offset() function #VALUE!
Looking at your post again it seems that you want to stay on offset 1 so the
formula should be J4: =OFFSET($B$4,1,0,1,1) and copied down. However, if you want to change the lookup value in the future you could have the value in a cell, say E3 and use the formula J4: =OFFSET($B$4,E3,0,1,1) Then you can enter a new number in E3 to change the offset value. Peter "H.C. Chen" wrote: You have set the Height of three so when the formula is copied it will come to empty cells and produce an error. But there's no empty cells in the Height of three! I make it clearer by this example : B4:B11 = {"data";11;22;33;44;55;66;77} J4 =OFFSET($B$4,1,0,3,1) = #VALUE! <-------- 11 is expected but why error? J5 =OFFSET($B$4,1,0,3,1) = 11 <--------------- Correct J6 =OFFSET($B$4,1,0,3,1) = 22 <----- I think it should be still 11, but why 22? -- 事情不耐做,問題不耐看。 "Billy Liddel" wrote: Offset uses a single cell as the reference. When You use a range name for the reference then Excel uses the first cell as the reference. You have set the Height of three so when the formula is copied it will come to empty cells and produce an error. Using the same data 1 to 8 in cells B4:B11 the following formula produces the sum of the next three rows. Note: The reference row is not absolute. =SUM(OFFSET($B4,0,0,3)) Not value is returned and the totals wil decrease when there is no value in the cell covered by the Height. Extend up and down and the value will eventually be zero. HTH Peter Atherton "H.C. Chen" wrote: 1. Name="Data" B4:B11 = {"data", 1,2,3,4,5,6,7} 2. formula =OFFSET(Data,1,0,3,1) 's value is 2 when at E6 , seems fine 3. But same formula becomes an error #VALUE! when at E5 Why different result happens at E5 and E6 with same formula? I think =OFFSET(Data,1,0,3,1) has nothing to do with its own position. -- 事情不耐做,問題不耐看。 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XL2002 - OFFSET function and LARGE function | Excel Worksheet Functions | |||
Use of OFFSET function | Excel Worksheet Functions | |||
Help with OffSet Function! | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
help with offset function | Excel Worksheet Functions |