Spreadsheet Database Functions 1

[davef2]

I frequently use the database "@functions" in Lotus 123 Release 5 to extract and analize tabular data my spreadsheets. I have tried to use the corresponding database functions in Excel versions up to and including XP with great difficulty. The main difference seems to be in the way the criterion is specified. In the old Lotus version, I can write the criteria directly in the function call whaeras in Excel I need to reference a range for the criteria. This may seem like a small difference, but I have been unable to create useful columns of these database function calls in Excel. For example:

Suppose I have a table named "TABLE1" (Table at bottom of post) that goes on with much more data. In Lotus, I can write a formula in cell A1 like:

@dsum($TABLE1,D1,COUNTRY=B1#AND#YEAR=C1)

Where D1 is the string "SALES", B1 and C1 might be other formulas whose results are "England" and "1999". In Excel, I can write a similar formula

=DSUM(TABLE1,"SALES",M5:N6)

but I have to reference a formatted, multirow range (M5:N6) for the criteria. This makes it very difficult to create a column of function calls that refer to criteria in corresponding columns.

I admit that I don't understand Excel pivot tables as well as I should, but I don't think I want a pivot table. I want a column of formulae that reference other specific columns for criteria that are determined by me, not by the pivot table. I hope someone out there can tell me that there is a way to do this in Excel.

TABLE1

DIV COUNTRY YEAR PRODUCT MARKET SALES
--- ------- ---- ------- ------ -----
Atlanta USA 1999 Nuts Aerospace 68.96
Chicago USA 1999 Nuts Aerospace 51.73
London England 1999 Nuts Aerospace 42.58
New Yor USA 1999 Nuts Aerospace 67.94
Atlanta USA 1999 Nuts Automotive 85.78
Chicago USA 1999 Nuts Automotive 52.87
London England 1999 Nuts Automotive 67.84
New Yor USA 1999 Nuts Automotive 72.74
Atlanta USA 1999 Bolts Aerospace 53.63
Chicago USA 1999 Bolts Aerospace 12.22
London England 1999 Bolts Aerospace 37.74
New Yor USA 1999 Bolts Aerospace 61.74
Atlanta USA 1999 Bolts Automotive 71.13
Chicago USA 1999 Bolts Automotive 43.86
London England 1999 Bolts Automotive 83.72
New Yor USA 1999 Bolts Automotive 10.84

 

[DaleWatson123321]

Dave,

As a HEAVY-DUTY user of the same Lotus 123 version 5, AND the DATABASE functions to which you refer, I can REALLY appreciate the difficulty you're facing in attempting to achieve the same results with Excel.

As you've found out, there is really NO COMPARISON between the two. Certainly Excel's Pivot Tables are very useful, but in reality "don't come close" to the EXTREMELY USEFUL and POWERFUL features contained with Lotus 123's database functions.

Perhaps "in my next lifetime", Microsoft will finally take a SERIOUS look at Lotus 123 version 5, and "borrow" some of these POWERFUL features.

In the meantime, I've been spending a "considerable" amount of time in attempting to achieve as much "database functionality" out of Excel as possible. While I haven't come anywhere close to Lotus 123 version 5, I have still been able to achieve "moderate success".

I've been able to identify some of the "quirks", and deal with them or work around them.

There is also a "bug" in this particular area of Excel. Specifically, when a user attempts to extract data to a SEPARATE sheet "manually" - using Data - Filter - Advanced Filter, the user gets an ERROR message saying that it is NOT possible.

This is NOT the case. Indeed it IS possible to extract data from one sheet to another using VBA code - the code related to this SAME Data - Filter - Advanced Filter.

As for the "quirks", they are mostly related to the use of "criteria". And there is another "bug" that says the criteria HAS to be on the SAME sheet as your data. Again, NOT so.

Dave, if you'd like to email me a copy of your file, I'd be happy to see what I can recommend. If necessary, a scaled down version is fine, and with sensitive data replaced with fictitious data that still reflects the type of data your're dealing with.

I'm at HOME this week, so use my HOME address.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca

 

[xlbo]

Methinks you need to look at array formulae (for multiple conditions) - SumProduct can also be useful

From your example
@dsum($TABLE1,D1,COUNTRY=B1#AND#YEAR=C1)

Array formula would be
{=SUM((B2:B100=$B$2)*(C2:C100=$C$2)*(F2:F100))}

Use Ctrl + Shift + Enter instead of enter.
To do a count, use:
{=SUM((B2:B100=$B$2)*(C2:C100=$C$2))}

HTH
Geoff