lotus 123 worksheet function conversion

[andresg1975]

i have a worksheet like this:

column c d e f

row 3 date number amount
row 4 08/31/2006 13610 294.96 @dsum($data,"amount",number=d4)

the data range contains this fields:

date number amount

if it contains number 13610, the function will return the amount

when i open it as an excel file the function returns #name? , how can i
create an
equivalent function in excel that will return the same result.

thanks a lot for your help

 

[Harlan Grove]

andresg1975 wrote...

i have a worksheet like this:

column c d e f

row 3 date number amount
row 4 08/31/2006 13610 294.96 @dsum($data,"amount",number=d4)

....

Short answer, use

=SUMPRODUCT(--(INDEX(Data,0,2)=D4),INDEX(Data,0,3))

hardcoding the field numbers, or more generally

=SUMPRODUCT(--(INDEX(Data,0,MATCH("number",INDEX(Data,1,0),0))=D4),
INDEX(Data,0,MATCH("amount",INDEX(Data,1,0),0)))

Excel's DSUM etc. functions are poor copies of 123 Release 2's
corresponding functions, i.e., they require the 3rd argument to be a
reference to a criteria range. Excel doesn't support criteria
expressions. On the other hand, you could try Excel's SQL.REQUEST
add-in function which allows you to use SQL queries, which are much
more powerful than 123's criteria expressions.

 

[andresg1975]

thanks a lot for your help

andresg1975 wrote...
....

Short answer, use

=SUMPRODUCT(--(INDEX(Data,0,2)=D4),INDEX(Data,0,3))

hardcoding the field numbers, or more generally

=SUMPRODUCT(--(INDEX(Data,0,MATCH("number",INDEX(Data,1,0),0))=D4),
INDEX(Data,0,MATCH("amount",INDEX(Data,1,0),0)))

Excel's DSUM etc. functions are poor copies of 123 Release 2's
corresponding functions, i.e., they require the 3rd argument to be a
reference to a criteria range. Excel doesn't support criteria
expressions. On the other hand, you could try Excel's SQL.REQUEST
add-in function which allows you to use SQL queries, which are much
more powerful than 123's criteria expressions.