Custom Function Desired

[FARAZ QURESHI]

I want to create a custom function like:

Public Function MyFormula(ARRAY1, ARRAY2, ARRAY3, STRING)
MyFormula=SUMPRODUCT(--(ARRAY1=STRING),--(ARRAY2>0),ARRAY3)

How to give it a correct and complete form?

Thanx!

 

[Joe]

I want to create a custom function like:

Public Function MyFormula(ARRAY1, ARRAY2, ARRAY3, STRING)
MyFormula=SUMPRODUCT(--(ARRAY1=STRING),--(ARRAY2>0),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:B10>D2,"1","0")*C1: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

 

[FARAZ QURESHI]

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!

 

[Bob Phillips]

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)

I want to create a custom function like:

Public Function MyFormula(ARRAY1, ARRAY2, ARRAY3, STRING)
MyFormula=SUMPRODUCT(--(ARRAY1=STRING),--(ARRAY2>0),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:B10>D2,"1","0")*C1: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

 

[FARAZ QURESHI]

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]

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)

 

[Joe]

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)










- 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