• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Array Sumproduct Function in VBA

V

Vadivelan

New Member
Dear Seniors,
I am new user to VBA & had created a Array sum product formula with multiple conditions and it works fine. Now I would like to get help from this forum to create the same formula in Excel VBA. Presently I am using named ranges in my formula. But I would like to use the formula by selecting the dynamic ranges.
Here is the formula I am using to calculate my result based on the Date in C1
{=SUMPRODUCT(IF(((INTPLANIFD>0)*(INTPLANIFD<=H$32)),1,IF(((INTPLANIFA>0)*(INTPLANIFA<=H$32)),0.8,IF(((INTPLANIFR>0)*(INTPLANIFR<=H$32)),0.6,0)))*(INTWF))}
where us INTPLANIFD, INTPLANIFA, INTPLANIFR are Named ranges which contains Dates & INTWF is a Number.

Actually I am using this formula to calculate the progress on a particular date.

Could you please assist me to create the formula. Herewith I had enclosed the sample file, In sheet named "CURVE" I am using this formula.

Thanks and Regards
R. Vadivelan
 

Attachments

  • MDR.xlsx
    73.9 KB · Views: 4
Dear Seniors,

Can I get your help on these above issue, It will be required for my project work. Since I am a new user to VBA I am unable to crack the code

Thanks and Regards
R. Vadivelan
 
Vadivelan

Your post is a cross post. Other thread here.

http://www.ozgrid.com/forum/showthread.php?t=189934

I assume you read this:

http://chandoo.org/forum/threads/new-users-please-read.294/

The relevant part:

    • Cross Posting, generally it is considered poor practice to cross post, that is to post the same question on several forums in the hope of getting a response quicker.
    • If you do cross post, please put that in your post.
    • Also if you have cross posted and get an answer elsewhere, have the courtesy of posting the answer here so other readers can learn from the answer also, as well as stopping people wasting there time on your answered question.
Click to expand...

Please be aware and courteous in future.

Take care

Smallman
 
Hi Small man,

I am very sorry for the cross post. I thought both are different sites..this will not happen next time. I had posted in 2 sites because I need the solution...but still my problem is not solved.

Hope to get reply in any one of the site.

Thanks and Regards
R. Vadivelan
 
Hi Narayan,

Could you please further assist me how to call this function. Also I had seen that code is not posted in module...I understood that the code is written for cell M5...I want it to be a dynamic one..i.e. I want to use this function similar to sumproduct

Thanks and Regards
R. Vadivelan
 
Hi Vadivelan ,

I am not able to understand what you wish to do. If you mean that you want a User Defined Function ( UDF ) , which you will use within a formula , then please mention the following :

1. Which cell or cells will you enter this formula ?

2. What are the parameters you will use when calling this function ?

3. What are the constants and variables in the formula you have posted ? Suppose this formula is used in cells J7 and AA23 ; between the formula in J7 and the formula in AA23 , what will remain the same , and what will change ?

Narayan
 
Hi Narayan,

Yes I want to User Defined Function which I will call in my formula.

1) In my there is a sheet called Curve. In the sheet I have 2 rows Plan and Actual, there I will call the formula

2) The Parameter I will use

Cut Off Date - Date (Used to calculate the progress at this date)
INTPLANIFD - Range of Dates (It will be same for all calculations, But I will give it as a parameter)
INTPLANIFA - Range of Dates (It will be same for all calculations, But I will give it as a parameter)
INTPLANIFR - Range of Dates (It will be same for all calculations, But I will give it as a parameter)
INTWF - Range of Numbers in percentage format (It will be same for all calculations, But I will give it as a parameter)

3) In my formula the variable will be only CutOffDate and the remaining parameters are constant.

My objective is to instead of typing the long formula in all cells, in order to avoid any mistakes I would like to create a UDF.

Hope I had clarified your queries, if not please let me know.

Thanks and Regards
R. Vadivelan
 
Hi Vadivelan ,

Where will the CUTOFFDATE parameter be used ? Will it take the place of H$32 in your originally posted formula ?

Narayan
 
You must log in or register to reply here.
Back
Top