Home |
Search |
Today's Posts |
#1
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
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
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
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
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
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
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
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
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
Sumproduct? | Excel Worksheet Functions | |||
how to use sumproduct | Excel Worksheet Functions | |||
SUMPRODUCT | Excel Worksheet Functions | |||
Using SumProduct in VB | Excel Discussion (Misc queries) |