problem with excel2007 table and dbfunctions

H

herman

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)
 
S

Sheeloo

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...
 
H

herman

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 said:
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 said:
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)
Click to expand...
Click to expand...
 
H

herman

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
Click to expand...
=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 said:
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)
Click to expand...
 
T

theresa

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
 
Top