Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct frustrates me to no end.
Hi,
I can't make sense of this and I need help see the error. I have defined two dynamic names. NewCall uses =OFFSET(Data!$AB$3,0,0,COUNTA(Data!$AB:$AB),1) ExistingCall uses =OFFSET(Data!$AC$3,0,0,COUNTA(Data!$AC:$AC),1) Both columns only contain numbers. I also have another one with dates defined as TrackDate with formula =OFFSET(Data!$AA$3,0,0,COUNTA(Data!$AA:$AA),1) So same formula just different range. Heres the funny part, if I use the formula =SUMPRODUCT((TrackDate<=$C $94)*(TrackDate=$B$94)*(TrackDate<""),ExistingCa ll) it works perfectly, but if I use instead =SUMPRODUCT((TrackDate<=$C $94)*(TrackDate=$B$94)*(TrackDate<""),NewCall) it gives a #value error. The problem is NewCall, because if I delete it from the formula it does return a result. This is where I am stumped, I can't figure out whats wrong with NewCall. in the Define Name window, when the refers to box is selected it highlits the range in the worksheet. The only difference between newcall and Existingcall is that existingcall only has one empty row at the bottom and NewCall has two rows. How can I troubleshoot this? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Define Names problem
I just found out something.
NewCall works if using the formula =OFFSET(Data!$AB$3,0,0,COUNTA(Data! $AC:$AC),1) Does it means that theres something wrong with AB rows. To check that the Offset formula is working, if I use CountA(newcall) and counta(existingcall) they both give the same result, which is correct. I also checked the row format to make sure it is set up as general. Any ideas? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct frustrates me to no end.
You should base the height of all the ranges on a "key" column. It sounds
like your DATE column should be the key column. So: TrackDate Refers to: =OFFSET(Data!$AA$3,0,0,COUNTA(Data!$AA:$AA)) NewCall Refers to: =OFFSET(Data!$AB$3,0,0,COUNTA(Data!$AA:$AA)) ExistingCall Refers to: =OFFSET(Data!$AC$3,0,0,COUNTA(Data!$AA:$AA)) That way each range is based on the height of TrackDate -- Biff Microsoft Excel MVP wrote in message ... Hi, I can't make sense of this and I need help see the error. I have defined two dynamic names. NewCall uses =OFFSET(Data!$AB$3,0,0,COUNTA(Data!$AB:$AB),1) ExistingCall uses =OFFSET(Data!$AC$3,0,0,COUNTA(Data!$AC:$AC),1) Both columns only contain numbers. I also have another one with dates defined as TrackDate with formula =OFFSET(Data!$AA$3,0,0,COUNTA(Data!$AA:$AA),1) So same formula just different range. Heres the funny part, if I use the formula =SUMPRODUCT((TrackDate<=$C $94)*(TrackDate=$B$94)*(TrackDate<""),ExistingCa ll) it works perfectly, but if I use instead =SUMPRODUCT((TrackDate<=$C $94)*(TrackDate=$B$94)*(TrackDate<""),NewCall) it gives a #value error. The problem is NewCall, because if I delete it from the formula it does return a result. This is where I am stumped, I can't figure out whats wrong with NewCall. in the Define Name window, when the refers to box is selected it highlits the range in the worksheet. The only difference between newcall and Existingcall is that existingcall only has one empty row at the bottom and NewCall has two rows. How can I troubleshoot this? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct frustrates me to no end.
Thanks for the help.
It makes sense, and I will do that. But I still dont understand why AB does not work? On Jan 29, 1:56*am, "T. Valko" wrote: You should base the height of all the ranges on a "key" column. It sounds like your DATE column should be the key column. So: TrackDate Refers to: =OFFSET(Data!$AA$3,0,0,COUNTA(Data!$AA:$AA)) NewCall Refers to: =OFFSET(Data!$AB$3,0,0,COUNTA(Data!$AA:$AA)) ExistingCall Refers to: =OFFSET(Data!$AC$3,0,0,COUNTA(Data!$AA:$AA)) That way each range is based on the height of TrackDate -- Biff Microsoft Excel MVP wrote in message ... Hi, I can't make sense of this and I need help see the error. I have defined two dynamic names. NewCall uses =OFFSET(Data!$AB$3,0,0,COUNTA(Data!$AB:$AB),1) ExistingCall uses =OFFSET(Data!$AC$3,0,0,COUNTA(Data!$AC:$AC),1) Both columns only contain numbers. I also have another one with dates defined as TrackDate with formula =OFFSET(Data!$AA$3,0,0,COUNTA(Data!$AA:$AA),1) So same formula just different range. Heres the funny part, if I use the formula =SUMPRODUCT((TrackDate<=$C $94)*(TrackDate=$B$94)*(TrackDate<""),ExistingCa ll) it works perfectly, but if I use instead =SUMPRODUCT((TrackDate<=$C $94)*(TrackDate=$B$94)*(TrackDate<""),NewCall) it gives a #value error. The problem is NewCall, because if I delete it from the formula it does return a result. This is where I am stumped, I can't figure out whats wrong with NewCall. in the Define Name window, when the refers to box is selected it highlits the range in the worksheet. The only difference between newcall and Existingcall is that existingcall only has one empty row at the bottom and NewCall has two rows. How can I troubleshoot this? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct frustrates me to no end.
But I still dont understand why AB does not work?
The only difference between newcall and Existingcall is that existingcall only has one empty row at the bottom and NewCall has two rows. All of your ranges might not be the same size (height). -- Biff Microsoft Excel MVP wrote in message ... Thanks for the help. It makes sense, and I will do that. But I still dont understand why AB does not work? On Jan 29, 1:56 am, "T. Valko" wrote: You should base the height of all the ranges on a "key" column. It sounds like your DATE column should be the key column. So: TrackDate Refers to: =OFFSET(Data!$AA$3,0,0,COUNTA(Data!$AA:$AA)) NewCall Refers to: =OFFSET(Data!$AB$3,0,0,COUNTA(Data!$AA:$AA)) ExistingCall Refers to: =OFFSET(Data!$AC$3,0,0,COUNTA(Data!$AA:$AA)) That way each range is based on the height of TrackDate -- Biff Microsoft Excel MVP wrote in message ... Hi, I can't make sense of this and I need help see the error. I have defined two dynamic names. NewCall uses =OFFSET(Data!$AB$3,0,0,COUNTA(Data!$AB:$AB),1) ExistingCall uses =OFFSET(Data!$AC$3,0,0,COUNTA(Data!$AC:$AC),1) Both columns only contain numbers. I also have another one with dates defined as TrackDate with formula =OFFSET(Data!$AA$3,0,0,COUNTA(Data!$AA:$AA),1) So same formula just different range. Heres the funny part, if I use the formula =SUMPRODUCT((TrackDate<=$C $94)*(TrackDate=$B$94)*(TrackDate<""),ExistingCa ll) it works perfectly, but if I use instead =SUMPRODUCT((TrackDate<=$C $94)*(TrackDate=$B$94)*(TrackDate<""),NewCall) it gives a #value error. The problem is NewCall, because if I delete it from the formula it does return a result. This is where I am stumped, I can't figure out whats wrong with NewCall. in the Define Name window, when the refers to box is selected it highlits the range in the worksheet. The only difference between newcall and Existingcall is that existingcall only has one empty row at the bottom and NewCall has two rows. How can I troubleshoot this? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct frustrates me to no end.
Thats what I thought, but using Counta gives me the same result for
either AB and AC, in this case 20. I dont understand this problem. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct frustrates me to no end.
Hi
What do you get if you try COUNT() on each of the ranges? You say the ranges only contain numeric values, but you also talk about ExistingCall having 1 empty row and Newcall having 2 empty rows. If they are numbers, and they are truly empty, then you wouldn't get a result of 20 in all cases, you would get 20, 19 and 18. I suspect the problem lies with the content of those supposedly "empty" cells. As Biff says, in any case, it is always better to use what you would expect to be the longest column of data as your count, and to use that for all ranges to get a consistent length. I always set a named range called lr (lastrow) as =COUNTA($AA:$AA) (as an example) then each range would be defined as =$AA3:INDEX($AA:$AA,lr) =$AB3:INDEX($AB:$AB,lr) etc. This method also avoids the use of the volatile Offset function. -- Regards Roger Govier wrote in message ... Thats what I thought, but using Counta gives me the same result for either AB and AC, in this case 20. I dont understand this problem. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct frustrates me to no end.
What do you get if you try COUNT() on each of the ranges? You say the ranges only contain numeric values, but you also talk about ExistingCall having 1 empty row and Newcall having 2 empty rows. If they are numbers, and they are truly empty, then you wouldn't get a result of 20 in all cases, you would get 20, 19 and 18. I suspect the problem lies with the content of those supposedly "empty" cells. That is my thinking too, but I did clear the cells and it always goes below two rows from the last one whille AC only goes one down. Is there a way to see whats in a cell? I even used clear from the menu, same result. As Biff says, in any case, it is always better to use what you would expect to be the longest column of data as your count, and to use that for all ranges to get a consistent length. I always set a named range called lr (lastrow) as =COUNTA($AA:$AA) (as an example) then each range would be defined as =$AA3:INDEX($AA:$AA,lr) =$AB3:INDEX($AB:$AB,lr) etc. This method also avoids the use of the volatile Offset function. Thanks for the sugestion. It is much more elgant and easy to use. I will start using it right away. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct frustrates me to no end.
Is there a way to see whats in a cell?
try =LEN(AB23) or whatever cell you want to test -- Regards Roger Govier wrote in message ... |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct frustrates me to no end.
=$AA3:INDEX($AA:$AA,lr)
=$AB3:INDEX($AB:$AB,lr) This method also avoids the use of the volatile Offset function. I think I've read somewhere that defined name formulas recalc on every calc so in essence they are volatile whether they use volatile functions or not. In any case, the above named formulas are "volatile" at least once when the particular file is opened. (I'm using Excel 2002 for this) Try this: Open a new wb Put some random numbers in A1:A5 InsertNameDefine Name: rng Refers to: =Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNT(Sheet1!$A:$A )) OK Enter this formula in C1: =SUM(rng) Save the file then close it. Open the file, don't do anything then close it. You'll get the "save changes" message. -- Biff Microsoft Excel MVP "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... Hi What do you get if you try COUNT() on each of the ranges? You say the ranges only contain numeric values, but you also talk about ExistingCall having 1 empty row and Newcall having 2 empty rows. If they are numbers, and they are truly empty, then you wouldn't get a result of 20 in all cases, you would get 20, 19 and 18. I suspect the problem lies with the content of those supposedly "empty" cells. As Biff says, in any case, it is always better to use what you would expect to be the longest column of data as your count, and to use that for all ranges to get a consistent length. I always set a named range called lr (lastrow) as =COUNTA($AA:$AA) (as an example) then each range would be defined as =$AA3:INDEX($AA:$AA,lr) =$AB3:INDEX($AB:$AB,lr) etc. This method also avoids the use of the volatile Offset function. -- Regards Roger Govier wrote in message ... Thats what I thought, but using Counta gives me the same result for either AB and AC, in this case 20. I dont understand this problem. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct frustrates me to no end.
Hi Biff
That might be so, on opening the workbook (or rather closing it), I don't know why. But try downloading Charles Williams test sheet for Volatile functions http://www.decisionmodels.com/Downlo...atileFuncs.zip Enter your range using Offset as a named range myRange, and use my method using Index and call in myrRange2 On Charles' sheet, enter =SUM(myRange) and =SUM(myRange2) in any 2 cells on the sheet, and copy the formula =CalcSeqCountRef(Axx) to refer to each of the cells holding those Calculations. Press F9, and watch the results. -- Regards Roger Govier "T. Valko" wrote in message ... =$AA3:INDEX($AA:$AA,lr) =$AB3:INDEX($AB:$AB,lr) This method also avoids the use of the volatile Offset function. I think I've read somewhere that defined name formulas recalc on every calc so in essence they are volatile whether they use volatile functions or not. In any case, the above named formulas are "volatile" at least once when the particular file is opened. (I'm using Excel 2002 for this) Try this: Open a new wb Put some random numbers in A1:A5 InsertNameDefine Name: rng Refers to: =Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNT(Sheet1!$A:$A )) OK Enter this formula in C1: =SUM(rng) Save the file then close it. Open the file, don't do anything then close it. You'll get the "save changes" message. -- Biff Microsoft Excel MVP "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... Hi What do you get if you try COUNT() on each of the ranges? You say the ranges only contain numeric values, but you also talk about ExistingCall having 1 empty row and Newcall having 2 empty rows. If they are numbers, and they are truly empty, then you wouldn't get a result of 20 in all cases, you would get 20, 19 and 18. I suspect the problem lies with the content of those supposedly "empty" cells. As Biff says, in any case, it is always better to use what you would expect to be the longest column of data as your count, and to use that for all ranges to get a consistent length. I always set a named range called lr (lastrow) as =COUNTA($AA:$AA) (as an example) then each range would be defined as =$AA3:INDEX($AA:$AA,lr) =$AB3:INDEX($AB:$AB,lr) etc. This method also avoids the use of the volatile Offset function. -- Regards Roger Govier wrote in message ... Thats what I thought, but using Counta gives me the same result for either AB and AC, in this case 20. I dont understand this problem. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct frustrates me to no end.
What is this spreadsheet supposed to do?
On Jan 29, 10:15*pm, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: Hi Biff That might be so, on opening the workbook (or rather closing it), I don't know why. But try downloading Charles Williams test sheet for Volatile functionshttp://www.decisionmodels.com/Downloads/VolatileFuncs.zip |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct frustrates me to no end.
This might explain what is happening with the INDEX formula.
http://tinyurl.com/2etk55 -- Biff Microsoft Excel MVP "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... Hi Biff That might be so, on opening the workbook (or rather closing it), I don't know why. But try downloading Charles Williams test sheet for Volatile functions http://www.decisionmodels.com/Downlo...atileFuncs.zip Enter your range using Offset as a named range myRange, and use my method using Index and call in myrRange2 On Charles' sheet, enter =SUM(myRange) and =SUM(myRange2) in any 2 cells on the sheet, and copy the formula =CalcSeqCountRef(Axx) to refer to each of the cells holding those Calculations. Press F9, and watch the results. -- Regards Roger Govier "T. Valko" wrote in message ... =$AA3:INDEX($AA:$AA,lr) =$AB3:INDEX($AB:$AB,lr) This method also avoids the use of the volatile Offset function. I think I've read somewhere that defined name formulas recalc on every calc so in essence they are volatile whether they use volatile functions or not. In any case, the above named formulas are "volatile" at least once when the particular file is opened. (I'm using Excel 2002 for this) Try this: Open a new wb Put some random numbers in A1:A5 InsertNameDefine Name: rng Refers to: =Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNT(Sheet1!$A:$A )) OK Enter this formula in C1: =SUM(rng) Save the file then close it. Open the file, don't do anything then close it. You'll get the "save changes" message. -- Biff Microsoft Excel MVP "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... Hi What do you get if you try COUNT() on each of the ranges? You say the ranges only contain numeric values, but you also talk about ExistingCall having 1 empty row and Newcall having 2 empty rows. If they are numbers, and they are truly empty, then you wouldn't get a result of 20 in all cases, you would get 20, 19 and 18. I suspect the problem lies with the content of those supposedly "empty" cells. As Biff says, in any case, it is always better to use what you would expect to be the longest column of data as your count, and to use that for all ranges to get a consistent length. I always set a named range called lr (lastrow) as =COUNTA($AA:$AA) (as an example) then each range would be defined as =$AA3:INDEX($AA:$AA,lr) =$AB3:INDEX($AB:$AB,lr) etc. This method also avoids the use of the volatile Offset function. -- Regards Roger Govier wrote in message ... Thats what I thought, but using Counta gives me the same result for either AB and AC, in this case 20. I dont understand this problem. |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct frustrates me to no end.
OK. So this can explain why it is volatile when opening the book, and the
"dirty" flag is set, hence the prompt to Save when closing. The Index() method is still far more efficient than using the truly volatile Offset() function, which will cause recalculation on each and every change within the workbook. Besides that, the formula is shorter to type<vbg -- Regards Roger Govier "T. Valko" wrote in message ... This might explain what is happening with the INDEX formula. http://tinyurl.com/2etk55 -- Biff Microsoft Excel MVP "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... Hi Biff That might be so, on opening the workbook (or rather closing it), I don't know why. But try downloading Charles Williams test sheet for Volatile functions http://www.decisionmodels.com/Downlo...atileFuncs.zip Enter your range using Offset as a named range myRange, and use my method using Index and call in myrRange2 On Charles' sheet, enter =SUM(myRange) and =SUM(myRange2) in any 2 cells on the sheet, and copy the formula =CalcSeqCountRef(Axx) to refer to each of the cells holding those Calculations. Press F9, and watch the results. -- Regards Roger Govier "T. Valko" wrote in message ... =$AA3:INDEX($AA:$AA,lr) =$AB3:INDEX($AB:$AB,lr) This method also avoids the use of the volatile Offset function. I think I've read somewhere that defined name formulas recalc on every calc so in essence they are volatile whether they use volatile functions or not. In any case, the above named formulas are "volatile" at least once when the particular file is opened. (I'm using Excel 2002 for this) Try this: Open a new wb Put some random numbers in A1:A5 InsertNameDefine Name: rng Refers to: =Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNT(Sheet1!$A:$A )) OK Enter this formula in C1: =SUM(rng) Save the file then close it. Open the file, don't do anything then close it. You'll get the "save changes" message. -- Biff Microsoft Excel MVP "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... Hi What do you get if you try COUNT() on each of the ranges? You say the ranges only contain numeric values, but you also talk about ExistingCall having 1 empty row and Newcall having 2 empty rows. If they are numbers, and they are truly empty, then you wouldn't get a result of 20 in all cases, you would get 20, 19 and 18. I suspect the problem lies with the content of those supposedly "empty" cells. As Biff says, in any case, it is always better to use what you would expect to be the longest column of data as your count, and to use that for all ranges to get a consistent length. I always set a named range called lr (lastrow) as =COUNTA($AA:$AA) (as an example) then each range would be defined as =$AA3:INDEX($AA:$AA,lr) =$AB3:INDEX($AB:$AB,lr) etc. This method also avoids the use of the volatile Offset function. -- Regards Roger Govier wrote in message ... Thats what I thought, but using Counta gives me the same result for either AB and AC, in this case 20. I dont understand this problem. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
Using UDF in SUMPRODUCT | Excel Worksheet Functions | |||
Sumproduct | Excel Discussion (Misc queries) | |||
Help with SUMPRODUCT | Excel Discussion (Misc queries) |