Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lotus 123 worksheet function conversion
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lotus 123 worksheet function conversion
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))=D 4), 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lotus 123 worksheet function conversion
thanks a lot for your help
"Harlan Grove" wrote: 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))=D 4), 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing a WorkSheet from a Function??? | Excel Worksheet Functions | |||
numerical integration | Excel Discussion (Misc queries) | |||
Changing worksheet cells from within a function | Setting up and Configuration of Excel | |||
changing type style and colour within a worksheet function | Excel Worksheet Functions | |||
Reference the worksheet from a multiple worksheet range function ( | Excel Worksheet Functions |