Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem with excel2007 table and dbfunctions
I try to encourage the use of tables and tablenames in Excel2007. However I get very strange behaviour using tablenames in dbfunctions like DSUM,DMAX etc. During a course I noticed that =DBMAX(tblTop5[#Alles];"pos2006";E2168:E2169) gives correctly 1890 =DBMAX(tblTop5;"pos2006";E2169:E2170) gives #WAARDE! ---- Why? =DBMAX(tblTop5;1;E2170:E2171) gives correctly 1890 =DBMAX(C2160:D2165;"pos2006";E2170:E2171) gives correctly 1890 Using the range-adresses always works fine. When I use the formula-step-by step in the erroneous case , I notice the tablename gets replaced by "C2160;D2165" instead of "C2160:D2165"! Why does the second formula fail? I don't understand - I think it should work. (sorry - only Dutch version of Excel available for me, so I gueess DBMAX is DMAX in the English version) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem with excel2007 table and dbfunctions
I think the fourth parameter contains criteria...
In E2168:E2169, E2168 should contain one of the column heading or column number in the table and E2169 should contain the condition... In second formula you have used E2169:E2170 which makes E2169 as the column heading or number... I think that is the problem... I prefer to use E1:E2 for one condition F1:F2 for second and so on avoiding overlapping ranges... "herman" wrote: I try to encourage the use of tables and tablenames in Excel2007. However I get very strange behaviour using tablenames in dbfunctions like DSUM,DMAX etc. During a course I noticed that =DBMAX(tblTop5[#Alles];"pos2006";E2168:E2169) gives correctly 1890 =DBMAX(tblTop5;"pos2006";E2169:E2170) gives #WAARDE! ---- Why? =DBMAX(tblTop5;1;E2170:E2171) gives correctly 1890 =DBMAX(C2160:D2165;"pos2006";E2170:E2171) gives correctly 1890 Using the range-adresses always works fine. When I use the formula-step-by step in the erroneous case , I notice the tablename gets replaced by "C2160;D2165" instead of "C2160:D2165"! Why does the second formula fail? I don't understand - I think it should work. (sorry - only Dutch version of Excel available for me, so I gueess DBMAX is DMAX in the English version) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem with excel2007 table and dbfunctions
this was just an example, please don't look at the criteria ranges - they
are all empty for the moment. (perhaps it's only in the Dutch version) can you try this out? "Sheeloo" wrote: I think the fourth parameter contains criteria... In E2168:E2169, E2168 should contain one of the column heading or column number in the table and E2169 should contain the condition... In second formula you have used E2169:E2170 which makes E2169 as the column heading or number... I think that is the problem... I prefer to use E1:E2 for one condition F1:F2 for second and so on avoiding overlapping ranges... "herman" wrote: I try to encourage the use of tables and tablenames in Excel2007. However I get very strange behaviour using tablenames in dbfunctions like DSUM,DMAX etc. During a course I noticed that =DBMAX(tblTop5[#Alles];"pos2006";E2168:E2169) gives correctly 1890 =DBMAX(tblTop5;"pos2006";E2169:E2170) gives #WAARDE! ---- Why? =DBMAX(tblTop5;1;E2170:E2171) gives correctly 1890 =DBMAX(C2160:D2165;"pos2006";E2170:E2171) gives correctly 1890 Using the range-adresses always works fine. When I use the formula-step-by step in the erroneous case , I notice the tablename gets replaced by "C2160;D2165" instead of "C2160:D2165"! Why does the second formula fail? I don't understand - I think it should work. (sorry - only Dutch version of Excel available for me, so I gueess DBMAX is DMAX in the English version) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem with excel2007 table and dbfunctions
better example:
A ============== number 5 9 7 number =DBAANTAL(Tabel1;"number";$A$6:$A$7) #WAARDE! 0 =DBAANTAL(Tabel1;1;$A$6:$A$7) 0 =DBAANTAL(Tabel1[#Alles];1;$A$6:$A$7) 3 =DBAANTAL(Tabel1[#Alles];"number";$A$6:$A$7) 3 Why does =DBAANTAL(Tabel1;"number";$A$6:$A$7) #WAARDE! gives an error while =DBAANTAL(Tabel1[#Alles];"number";$A$6:$A$7) 3 does work correctly end even worse: =DBAANTAL(Tabel1;1;$A$6:$A$7) 0 gives an erroneous result!! "herman" wrote: I try to encourage the use of tables and tablenames in Excel2007. However I get very strange behaviour using tablenames in dbfunctions like DSUM,DMAX etc. During a course I noticed that =DBMAX(tblTop5[#Alles];"pos2006";E2168:E2169) gives correctly 1890 =DBMAX(tblTop5;"pos2006";E2169:E2170) gives #WAARDE! ---- Why? =DBMAX(tblTop5;1;E2170:E2171) gives correctly 1890 =DBMAX(C2160:D2165;"pos2006";E2170:E2171) gives correctly 1890 Using the range-adresses always works fine. When I use the formula-step-by step in the erroneous case , I notice the tablename gets replaced by "C2160;D2165" instead of "C2160:D2165"! Why does the second formula fail? I don't understand - I think it should work. (sorry - only Dutch version of Excel available for me, so I gueess DBMAX is DMAX in the English version) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem with excel2007 table and dbfunctions
Dear Herman
I am also having trobules getting the functions DMAX, DAVERAGE and DCOUNT to work with excel. The workaround to the problem appears to put the header row into the table twice so that the criteria can be satisfied. For some reason probably a bug all my results were returning zeros until I put the header in both row 1 and row 2. I am trying to search to see if anyone else has problems with these dbfunctions in Excel 2007 -- Dr. Theresa A Kraft, PMP "herman" wrote: I try to encourage the use of tables and tablenames in Excel2007. However I get very strange behaviour using tablenames in dbfunctions like DSUM,DMAX etc. During a course I noticed that =DBMAX(tblTop5[#Alles];"pos2006";E2168:E2169) gives correctly 1890 =DBMAX(tblTop5;"pos2006";E2169:E2170) gives #WAARDE! ---- Why? =DBMAX(tblTop5;1;E2170:E2171) gives correctly 1890 =DBMAX(C2160:D2165;"pos2006";E2170:E2171) gives correctly 1890 Using the range-adresses always works fine. When I use the formula-step-by step in the erroneous case , I notice the tablename gets replaced by "C2160;D2165" instead of "C2160:D2165"! Why does the second formula fail? I don't understand - I think it should work. (sorry - only Dutch version of Excel available for me, so I gueess DBMAX is DMAX in the English version) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel2007 pivot table chart x-axis | Charts and Charting in Excel | |||
Pivot table range selection changes when opening 2003 xls in Excel2007 | Excel Worksheet Functions | |||
VBA project opening problem in Excel2007 | Excel Discussion (Misc queries) | |||
Excel2007 Pivot Table problem | Excel Discussion (Misc queries) | |||
Problem with Histogram function of EXCEL2007 BETA | Charts and Charting in Excel |