I have a workbook that contains two worksheets: sheet 1 is a control list and sheet 2 uses sumproduct to get info from constrol list. The following formula is in sheet 2.
Is it possible to convert the following Excel formula to user defined function?
IF(SUMPRODUCT(('ControlList'!$N:$N=G$3)*('ControlList'!$R:$R=$A5),'ControlList'!$A:$A)>0,SUMPRODUCT(('ControlList'!$N:$N=G$3)*('ControlList'!$R:$R=$A5),'ControlList'!$A:$A),"")
Thank you in advance.
Have you tried:
?
Please Login or Register to view this content.
Note - I think there is a limit to the number of characters evaluate can cope with so you might need to split it up. I didn't know which bits were variable/fixed so just changed the stuff without $ signs to input variables.
Yes, I guess, but why would you want to do that?
If it's because it is slow, it will be because you have full column references. And it will ned to be evaluated twice, once to see if the value is zero, and again for the actual result. So, you're multiplying a million+ values by a million+ values by a million+ values ... twice.
You'd be better creating Dynamic Named Ranges, or, even better, converting your data to a Table and using structured Table references.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Interestingly, ragulduy's profile says Excel 2003. You can't have full column references in Excel 2003 and it is bad practice in more recent versions.
Regards, TMS
Last edited by TMS; 04-03-2014 at 09:59 AM.
Thanks but it seems the UFD does not work out.
I would like the cells ("G$3"), cells ("$A5") to be dynamic and the formula should also work when the cells are cells ("H$3"), Cells ("$A6").
The Excel function runs too slow so I need a UDF to solve the problem. Please help.
Did you read my reply. It's slow because of the full column references. No other reason
Regards, TMS
@OP
I agree with TMS, the userdefined function will just be the same computationally (if not worse). I had assumed you just didn't want to type out the whole formula every time.
Yes you are right. I have changed the formula by narrowing the range. The formula runs faster now.
But may I still want to learn how to use a dynamic range in the UDF because the control list is getting longer.
Thank you.
I suggest you post a sample workbook with some typical data. Then we can help you to create a Dynamic Named Range, or two, or three. Or, better, a Table.
A UDF is not the answer. Especially, if it simply evaluates a worksheet formula. Given that you are looking at 3 columns, you'd have to try to make it flexible enough to cope with whatever ranges you needed it to use. And it would only be relevant to a specific task.
Regards, TMS
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks