|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
January 15th, 2008, 07:39 AM
posted to microsoft.public.excel,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
|
|||
|
|||
#VALUE! from SUMPRODUCT
I have a problem with SUMPRODUCT. If I use this function:
=SUMPRODUCT($I$58:$I$108;OFFSET($H$58;58-ROW();0;51;1)) I get #VALUE! error. But if I calculate =58-ROW() in a new cell (K58) and uses this cell instead: =SUMPRODUCT($I$58:$I$108;OFFSET($H$58;K58;0;51;1)) then it works fine. What is wrong with the "58-ROW()" calculation inside the OFFSET function? Thanks in advance, Regards, 
|
|
#2
January 15th, 2008, 08:13 AM
posted to microsoft.public.excel,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#VALUE! from SUMPRODUCT
Forgot to tell, using version 2002 SP3.
Regards, "gte" wrote in message ... I have a problem with SUMPRODUCT. If I use this function: =SUMPRODUCT($I$58:$I$108;OFFSET($H$58;58-ROW();0;51;1)) I get #VALUE! error. But if I calculate =58-ROW() in a new cell (K58) and uses this cell instead: =SUMPRODUCT($I$58:$I$108;OFFSET($H$58;K58;0;51;1)) then it works fine. What is wrong with the "58-ROW()" calculation inside the OFFSET function? Thanks in advance, Regards,
|
|
#3
January 15th, 2008, 08:28 AM
posted to microsoft.public.excel,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#VALUE! from SUMPRODUCT
I can't explain exactly what the cause is as it relates to OFFSET but the
problem is being caused by the ROW function. ROW returns an array. Even if it's a single element it's still an array. So, this is what's returning the error: OFFSET($H$58,{n},0,51,1) This will work: =SUMPRODUCT($I$58:$I$108,OFFSET($H$58,58-INDEX(ROW(),1),0,51,1)) -- Biff Microsoft Excel MVP "gte" wrote in message ... I have a problem with SUMPRODUCT. If I use this function: =SUMPRODUCT($I$58:$I$108;OFFSET($H$58;58-ROW();0;51;1)) I get #VALUE! error. But if I calculate =58-ROW() in a new cell (K58) and uses this cell instead: =SUMPRODUCT($I$58:$I$108;OFFSET($H$58;K58;0;51;1)) then it works fine. What is wrong with the "58-ROW()" calculation inside the OFFSET function? Thanks in advance, Regards,
|
|
#4
January 15th, 2008, 08:39 AM
posted to microsoft.public.excel,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#VALUE! from SUMPRODUCT
Was your original formula
=SUMPRODUCT($I$58:$I$108;OFFSET($H$58;58-ROW();0;51;1)) sitting in row 58? If it wasn't, then it wouldn't return the same result. -- David Biddulph "gte" wrote in message ... I have a problem with SUMPRODUCT. If I use this function: =SUMPRODUCT($I$58:$I$108;OFFSET($H$58;58-ROW();0;51;1)) I get #VALUE! error. But if I calculate =58-ROW() in a new cell (K58) and uses this cell instead: =SUMPRODUCT($I$58:$I$108;OFFSET($H$58;K58;0;51;1)) then it works fine. What is wrong with the "58-ROW()" calculation inside the OFFSET function? Thanks in advance, Regards,
|
|
#5
January 15th, 2008, 08:52 AM
posted to microsoft.public.excel,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#VALUE! from SUMPRODUCT
That's it :-)
Thanks, "T. Valko" wrote in message ... I can't explain exactly what the cause is as it relates to OFFSET but the problem is being caused by the ROW function. ROW returns an array. Even if it's a single element it's still an array. So, this is what's returning the error: OFFSET($H$58,{n},0,51,1) This will work: =SUMPRODUCT($I$58:$I$108,OFFSET($H$58,58-INDEX(ROW(),1),0,51,1)) -- Biff Microsoft Excel MVP "gte" wrote in message ... I have a problem with SUMPRODUCT. If I use this function: =SUMPRODUCT($I$58:$I$108;OFFSET($H$58;58-ROW();0;51;1)) I get #VALUE! error. But if I calculate =58-ROW() in a new cell (K58) and uses this cell instead: =SUMPRODUCT($I$58:$I$108;OFFSET($H$58;K58;0;51;1)) then it works fine. What is wrong with the "58-ROW()" calculation inside the OFFSET function? Thanks in advance, Regards,
|
|
#6
January 15th, 2008, 08:56 AM
posted to microsoft.public.excel,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#VALUE! from SUMPRODUCT
Yes it is in row 58. But the problem is row(), this returns an array. Using
INDEX(ROW();1) returns an integer, then OFFSET works fine (see another message in this thread) Thanks for replies... "David Biddulph" groups [at] biddulph.org.uk wrote in message ... Was your original formula =SUMPRODUCT($I$58:$I$108;OFFSET($H$58;58-ROW();0;51;1)) sitting in row 58? If it wasn't, then it wouldn't return the same result. -- David Biddulph "gte" wrote in message ... I have a problem with SUMPRODUCT. If I use this function: =SUMPRODUCT($I$58:$I$108;OFFSET($H$58;58-ROW();0;51;1)) I get #VALUE! error. But if I calculate =58-ROW() in a new cell (K58) and uses this cell instead: =SUMPRODUCT($I$58:$I$108;OFFSET($H$58;K58;0;51;1)) then it works fine. What is wrong with the "58-ROW()" calculation inside the OFFSET function? Thanks in advance, Regards,
|
|
#7
January 15th, 2008, 09:07 AM
posted to microsoft.public.excel,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#VALUE! from SUMPRODUCT
=SUMPRODUCT($I$58:$I$108;OFFSET($H$58;58-MAX(ROW());0;51;1))
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "gte" wrote in message ... I have a problem with SUMPRODUCT. If I use this function: =SUMPRODUCT($I$58:$I$108;OFFSET($H$58;58-ROW();0;51;1)) I get #VALUE! error. But if I calculate =58-ROW() in a new cell (K58) and uses this cell instead: =SUMPRODUCT($I$58:$I$108;OFFSET($H$58;K58;0;51;1)) then it works fine. What is wrong with the "58-ROW()" calculation inside the OFFSET function? Thanks in advance, Regards,
|
|
#8
January 15th, 2008, 09:24 AM
posted to microsoft.public.excel,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#VALUE! from SUMPRODUCT
Thanks for the reply
gte "Bob Phillips" wrote in message ... =SUMPRODUCT($I$58:$I$108;OFFSET($H$58;58-MAX(ROW());0;51;1)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "gte" wrote in message ... I have a problem with SUMPRODUCT. If I use this function: =SUMPRODUCT($I$58:$I$108;OFFSET($H$58;58-ROW();0;51;1)) I get #VALUE! error. But if I calculate =58-ROW() in a new cell (K58) and uses this cell instead: =SUMPRODUCT($I$58:$I$108;OFFSET($H$58;K58;0;51;1)) then it works fine. What is wrong with the "58-ROW()" calculation inside the OFFSET function? Thanks in advance, Regards,
|
|
#9
January 15th, 2008, 06:16 PM
posted to microsoft.public.excel,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#VALUE! from SUMPRODUCT
You're welcome!
-- Biff Microsoft Excel MVP "gte" wrote in message ... That's it :-) Thanks, "T. Valko" wrote in message ... I can't explain exactly what the cause is as it relates to OFFSET but the problem is being caused by the ROW function. ROW returns an array. Even if it's a single element it's still an array. So, this is what's returning the error: OFFSET($H$58,{n},0,51,1) This will work: =SUMPRODUCT($I$58:$I$108,OFFSET($H$58,58-INDEX(ROW(),1),0,51,1)) -- Biff Microsoft Excel MVP "gte" wrote in message ... I have a problem with SUMPRODUCT. If I use this function: =SUMPRODUCT($I$58:$I$108;OFFSET($H$58;58-ROW();0;51;1)) I get #VALUE! error. But if I calculate =58-ROW() in a new cell (K58) and uses this cell instead: =SUMPRODUCT($I$58:$I$108;OFFSET($H$58;K58;0;51;1)) then it works fine. What is wrong with the "58-ROW()" calculation inside the OFFSET function? Thanks in advance, Regards,
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
