lotus 123 worksheet function conversion

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

andresg1975 - 31 Oct 2006 21:36 GMT

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

Reply to this Message

Harlan Grove - 31 Oct 2006 22:24 GMT

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.

Reply to this Message

andresg1975 - 01 Nov 2006 00:01 GMT

thanks a lot for your help

> andresg1975 wrote...
> >i have a worksheet like this:
[quoted text clipped - 20 lines]
> add-in function which allows you to use SQL queries, which are much
> more powerful than 123's criteria expressions.

Reply to this Message