| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Function Desired
I want to create a custom function like:
Public Function MyFormula(ARRAY1, ARRAY2, ARRAY3, STRING) MyFormula=SUMPRODUCT(--(ARRAY1=STRING),--(ARRAY20),ARRAY3) How to give it a correct and complete form? Thanx! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Custom Function Desired
On Dec 27, 8:54*am, FARAZ QURESHI
wrote: I want to create a custom function like: Public Function MyFormula(ARRAY1, ARRAY2, ARRAY3, STRING) MyFormula=SUMPRODUCT(--(ARRAY1=STRING),--(ARRAY20),ARRAY3) How to give it a correct and complete form? Thanx! Hi Faraz, you dont have to create a custom function. Assume the following ----------------- Array1 = A1:A10 Array2 = B1:B10 Array3 = C1:C10 STRING = D1 Limit = D2 1. Write the following code into a new cell. (Do not press ENTER) =SUM(IF(A1:A10=D1,"1","0")*IF(B1:B10D2,"1","0")*C 1:C10) 2. Press CTRL + SHIFT + ENTER. this will encapsulate it in { }. Now it is an array and should work for you. Note: Make sure that the array size same everywhere. HTH Joe |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Custom Function Desired
I know how to determine such a result. But I want to create a custom function!
A further refined formula is the one already presented in the body! "Joe" wrote: On Dec 27, 8:54 am, FARAZ QURESHI wrote: I want to create a custom function like: Public Function MyFormula(ARRAY1, ARRAY2, ARRAY3, STRING) MyFormula=SUMPRODUCT(--(ARRAY1=STRING),--(ARRAY20),ARRAY3) How to give it a correct and complete form? Thanx! Hi Faraz, you dont have to create a custom function. Assume the following ----------------- Array1 = A1:A10 Array2 = B1:B10 Array3 = C1:C10 STRING = D1 Limit = D2 1. Write the following code into a new cell. (Do not press ENTER) =SUM(IF(A1:A10=D1,"1","0")*IF(B1:B10D2,"1","0")*C 1:C10) 2. Press CTRL + SHIFT + ENTER. this will encapsulate it in { }. Now it is an array and should work for you. Note: Make sure that the array size same everywhere. HTH Joe |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Custom Function Desired
See response in excel.misc. You know better than to multi-post by now!
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joe" wrote in message ... On Dec 27, 8:54 am, FARAZ QURESHI wrote: I want to create a custom function like: Public Function MyFormula(ARRAY1, ARRAY2, ARRAY3, STRING) MyFormula=SUMPRODUCT(--(ARRAY1=STRING),--(ARRAY20),ARRAY3) How to give it a correct and complete form? Thanx! Hi Faraz, you dont have to create a custom function. Assume the following ----------------- Array1 = A1:A10 Array2 = B1:B10 Array3 = C1:C10 STRING = D1 Limit = D2 1. Write the following code into a new cell. (Do not press ENTER) =SUM(IF(A1:A10=D1,"1","0")*IF(B1:B10D2,"1","0")*C 1:C10) 2. Press CTRL + SHIFT + ENTER. this will encapsulate it in { }. Now it is an array and should work for you. Note: Make sure that the array size same everywhere. HTH Joe |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Custom Function Desired
THANX BOB!!!!!!!!!!!!!
U really made my problems solved! I am really sorry for double posting but I was actually receiving no response and had presumed that it might have been overlooked! Thanx again buddy! "Bob Phillips" wrote: See response in excel.misc. You know better than to multi-post by now! -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joe" wrote in message ... On Dec 27, 8:54 am, FARAZ QURESHI wrote: I want to create a custom function like: Public Function MyFormula(ARRAY1, ARRAY2, ARRAY3, STRING) MyFormula=SUMPRODUCT(--(ARRAY1=STRING),--(ARRAY20),ARRAY3) How to give it a correct and complete form? Thanx! Hi Faraz, you dont have to create a custom function. Assume the following ----------------- Array1 = A1:A10 Array2 = B1:B10 Array3 = C1:C10 STRING = D1 Limit = D2 1. Write the following code into a new cell. (Do not press ENTER) =SUM(IF(A1:A10=D1,"1","0")*IF(B1:B10D2,"1","0")*C 1:C10) 2. Press CTRL + SHIFT + ENTER. this will encapsulate it in { }. Now it is an array and should work for you. Note: Make sure that the array size same everywhere. HTH Joe |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Custom Function Desired
Faraz,
It generally won't get overlooked as most of us will check out all of the major Excel groups. If it isn't getting answered it will probably mean that there are few people online, so just be a bit patient. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "FARAZ QURESHI" wrote in message ... THANX BOB!!!!!!!!!!!!! U really made my problems solved! I am really sorry for double posting but I was actually receiving no response and had presumed that it might have been overlooked! Thanx again buddy! "Bob Phillips" wrote: See response in excel.misc. You know better than to multi-post by now! -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joe" wrote in message ... On Dec 27, 8:54 am, FARAZ QURESHI wrote: I want to create a custom function like: Public Function MyFormula(ARRAY1, ARRAY2, ARRAY3, STRING) MyFormula=SUMPRODUCT(--(ARRAY1=STRING),--(ARRAY20),ARRAY3) How to give it a correct and complete form? Thanx! Hi Faraz, you dont have to create a custom function. Assume the following ----------------- Array1 = A1:A10 Array2 = B1:B10 Array3 = C1:C10 STRING = D1 Limit = D2 1. Write the following code into a new cell. (Do not press ENTER) =SUM(IF(A1:A10=D1,"1","0")*IF(B1:B10D2,"1","0")*C 1:C10) 2. Press CTRL + SHIFT + ENTER. this will encapsulate it in { }. Now it is an array and should work for you. Note: Make sure that the array size same everywhere. HTH Joe |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Custom Function Desired
On Dec 27 2007, 3:47*pm, "Bob Phillips" wrote:
Faraz, It generally won't get overlooked as most of us will check out all of the major Excel groups. If it isn't getting answered it will probably mean that there are few people online, so just be a bit patient. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "FARAZ QURESHI" wrote in message ... THANX BOB!!!!!!!!!!!!! U really made my problems solved! I am really sorry for double posting but I was actually receiving no response and had presumed that it might have been overlooked! Thanx again buddy! "Bob Phillips" wrote: See response in excel.misc. You know better than to multi-post by now! -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joe" wrote in message .... On Dec 27, 8:54 am, FARAZ QURESHI wrote: I want to create a custom function like: Public Function MyFormula(ARRAY1, ARRAY2, ARRAY3, STRING) MyFormula=SUMPRODUCT(--(ARRAY1=STRING),--(ARRAY20),ARRAY3) How to give it a correct and complete form? Thanx! Hi Faraz, you dont have to create a custom function. Assume the following ----------------- Array1 = *A1:A10 Array2 = *B1:B10 Array3 = *C1:C10 STRING = *D1 Limit *= *D2 1. Write the following code into a new cell. (Do not press ENTER) =SUM(IF(A1:A10=D1,"1","0")*IF(B1:B10D2,"1","0")*C 1:C10) 2. Press CTRL + SHIFT + ENTER. this will encapsulate it in { }. *Now it is an array and should work for you. Note: Make sure that the array size same everywhere. HTH Joe- Hide quoted text - - Show quoted text - Ahh.. That works better.. This is new to me.. I used to the Array Way.. But what is the significance of "--". Is it that we have to put "--" before the array comparison ?? Thanks Joe |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Add-in - add a custom function to a custom group. | Excel Programming | |||
| CUSTOM FUNCTION DESIRED | Excel Discussion (Misc queries) | |||
| which function can get the desired result? | Excel Worksheet Functions | |||
| Custom file menu used to redirect to desired sheet | Excel Programming | |||
| Adding a custom function to the default excel function list | Excel Programming | |||
Hybrid Mode
