PDA

View Full Version : Sleeper: Convert Array Formula to VBA



wezred
07-20-2005, 07:41 PM
I have this formula:

{=+PERCENTILE(IF((HighestPaid!$D$8:$D$1012=$A35)*(IF($I$2="Yes", (HighestPaid!$Z$8:$Z$1012=$H$2))+IF($I$3="Yes", (HighestPaid!$Z$8:$Z$1012=$H$3))+IF($I$4="Yes", (HighestPaid!$Z$8:$Z$1012=$H$4))+IF($I$5="Yes", (HighestPaid!$Z$8:$Z$1012=$H$5))+IF($I$6="Yes", (HighestPaid!$Z$8:$Z$1012=$H$6))+IF($I$7="Yes", (HighestPaid!$Z$8:$Z$1012=$H$7))+IF($I$8="Yes", (HighestPaid!$Z$8:$Z$1012=$H$8))+IF($I$9="Yes", (HighestPaid!$Z$8:$Z$1012=$H$9))+IF($I$10="Yes", (HighestPaid!$Z$8:$Z$1012=$H$10)))*IF($B$2=1, 1, (HighestPaid!$AA$8:$AA$1012=$B$3))*IF($C$2=1, 1, (HighestPaid!$AB$8:$AB$1012=$C$3))*IF($D$2=1, 1, (HighestPaid!$AC$8:$AC$1012=$D$3))*IF($E$2=1, 1, (HighestPaid!$Y$8:$Y$1012=$E$2))*ISNUMBER(HighestPaid!$H$8:$H$1012)*IF($B$2 5="Yes", ISNUMBER(HighestPaid!$G$8:$G$1012), 1)*IF($B$26="Yes", ISNUMBER(HighestPaid!$J$8:$J$1012), 1), HighestPaid!$H$8:$H$1012+IF($B$25="Yes", HighestPaid!$G$8:$G$1012, 0)+IF($B$26="Yes", HighestPaid!$J$8:$J$1012, 0)), B$34)}

It has lots of options to include or exclude a certain group if you choose "Yes" or "No". This formula is incomplete as I've still about 20 more variables to input but when I do, it says "The formula is too long". Hence I'm wondering if there was a way to convert this array formula to VBA. Please help. Thanks.

Another option would be to change the coding for of this line in the formula:

(IF($I$2="Yes", (HighestPaid!$Z$8:$Z$1012=$H$2))+IF($I$3="Yes", (HighestPaid!$Z$8:$Z$1012=$H$3))+IF($I$4="Yes", (HighestPaid!$Z$8:$Z$1012=$H$4))+IF($I$5="Yes", (HighestPaid!$Z$8:$Z$1012=$H$5))+IF($I$6="Yes", (HighestPaid!$Z$8:$Z$1012=$H$6))+IF($I$7="Yes", (HighestPaid!$Z$8:$Z$1012=$H$7))+IF($I$8="Yes", (HighestPaid!$Z$8:$Z$1012=$H$8))+IF($I$9="Yes", (HighestPaid!$Z$8:$Z$1012=$H$9))+IF($I$10="Yes", (HighestPaid!$Z$8:$Z$1012=$H$10)))

to something like this:

IF($I$2:$I$10="Yes",(HighestPaid!$Z$8:$Z$1012=$H$2:$H$10))

but it doesn't work as excel doesn't seem to understand $H$2:$H$10, i think.

Any solution is appreciated. Thank you.

Brandtrock
07-20-2005, 09:00 PM
With as many variables as you have, an attached example would be a great help in getting a solution coded for you. Using the Go Advanced button (middle one) in the Quick Reply area will take you to the area that allows attachments to be added. Using the Post Reply button does the same thing.

HTH,

Bob Phillips
07-21-2005, 02:45 AM
I have this formula:

{=+PERCENTILE(IF((HighestPaid!$D$8:$D$1012=$A35)*(IF($I$2="Yes", (HighestPaid!$Z$8:$Z$1012=$H$2))+IF($I$3="Yes", (HighestPaid!$Z$8:$Z$1012=$H$3))+IF($I$4="Yes", (HighestPaid!$Z$8:$Z$1012=$H$4))+IF($I$5="Yes", (HighestPaid!$Z$8:$Z$1012=$H$5))+IF($I$6="Yes", (HighestPaid!$Z$8:$Z$1012=$H$6))+IF($I$7="Yes", (HighestPaid!$Z$8:$Z$1012=$H$7))+IF($I$8="Yes", (HighestPaid!$Z$8:$Z$1012=$H$8))+IF($I$9="Yes", (HighestPaid!$Z$8:$Z$1012=$H$9))+IF($I$10="Yes", (HighestPaid!$Z$8:$Z$1012=$H$10)))*IF($B$2=1, 1, (HighestPaid!$AA$8:$AA$1012=$B$3))*IF($C$2=1, 1, (HighestPaid!$AB$8:$AB$1012=$C$3))*IF($D$2=1, 1, (HighestPaid!$AC$8:$AC$1012=$D$3))*IF($E$2=1, 1, (HighestPaid!$Y$8:$Y$1012=$E$2))*ISNUMBER(HighestPaid!$H$8:$H$1012)*IF($B$2 5="Yes", ISNUMBER(HighestPaid!$G$8:$G$1012), 1)*IF($B$26="Yes", ISNUMBER(HighestPaid!$J$8:$J$1012), 1), HighestPaid!$H$8:$H$1012+IF($B$25="Yes", HighestPaid!$G$8:$G$1012, 0)+IF($B$26="Yes", HighestPaid!$J$8:$J$1012, 0)), B$34)}

It has lots of options to include or exclude a certain group if you choose "Yes" or "No". This formula is incomplete as I've still about 20 more variables to input but when I do, it says "The formula is too long". Hence I'm wondering if there was a way to convert this array formula to VBA. Please help. Thanks.

Another option would be to change the coding for of this line in the formula:

(IF($I$2="Yes", (HighestPaid!$Z$8:$Z$1012=$H$2))+IF($I$3="Yes", (HighestPaid!$Z$8:$Z$1012=$H$3))+IF($I$4="Yes", (HighestPaid!$Z$8:$Z$1012=$H$4))+IF($I$5="Yes", (HighestPaid!$Z$8:$Z$1012=$H$5))+IF($I$6="Yes", (HighestPaid!$Z$8:$Z$1012=$H$6))+IF($I$7="Yes", (HighestPaid!$Z$8:$Z$1012=$H$7))+IF($I$8="Yes", (HighestPaid!$Z$8:$Z$1012=$H$8))+IF($I$9="Yes", (HighestPaid!$Z$8:$Z$1012=$H$9))+IF($I$10="Yes", (HighestPaid!$Z$8:$Z$1012=$H$10)))

to something like this:

IF($I$2:$I$10="Yes",(HighestPaid!$Z$8:$Z$1012=$H$2:$H$10))

but it doesn't work as excel doesn't seem to understand $H$2:$H$10, i think.

Any solution is appreciated. Thank you.

You could reduce it substantially by adding a few names, e.g.

HPZ - HighestPaid!$Z$8:$Z$1012
HPAA - HighestPaid!$AA$8:$AA$1012

and substitute the names, will reduce it considerably.

wezred
07-21-2005, 08:27 PM
Thanks for the suggestion. I tried using Define Names but the formula is still too long (even if my define names are 1 letter).

I can't attached the file here for you as it's big and complicating and confidential.

I'm attaching a different file for you to look at and if you could solve this, it'll definitely solve my problem.

Note: You cannot use statements like "if(a1=1,b1,". You must use arrays or groupings as shown in the formula. Reason is if there is 10,000 different cells to go through, this statement would be too long.

Bob Phillips
07-22-2005, 01:59 AM
Thanks for the suggestion. I tried using Define Names but the formula is still too long (even if my define names are 1 letter).

I can't attached the file here for you as it's big and complicating and confidential.

I'm attaching a different file for you to look at and if you could solve this, it'll definitely solve my problem.

Note: You cannot use statements like "if(a1=1,b1,". You must use arrays or groupings as shown in the formula. Reason is if there is 10,000 different cells to go through, this statement would be too long.

This array formula works with the data presented


=SUMPRODUCT(($D$1:$D$13=TRANSPOSE((IF($A$1:$A$5=1,$B$1:$B$5,-1))))*($E$1:$E$13))

wezred
07-22-2005, 02:50 AM
Thanks a lot. It works. I refined it further to include:


=PERCENTILE(IF($D$1:$D$13=TRANSPOSE((IF($A$1:$A$5=1,$B$1:$B$5))),($E$1:$E$1 3)),0.25)

Was wondering how the Transpose function work. Will read up. Thanks.

Bob Phillips
07-22-2005, 02:58 AM
Thanks a lot. It works. I refined it further to include:

=PERCENTILE(IF($D$1:$D$13=TRANSPOSE((IF($A$1:$A$5=1,$B$1:$B$5))),($E$1:$E$1 3)),0.25)


Yeah, I left that bit for you :devil:



Was wondering how the Transpose function work. Will read up. Thanks.

When SUMPRODUCT handles an array of values, which is what that inner IF test creates, it needs it in ROW format. We were getting it in COLUMN format, so we just TRANSPOSEd it.

.

Zack Barresse
07-22-2005, 04:26 PM
.. you can tell the difference of what xld is talking about if going through the Formula Auditor, and checking if the values are semicolon ( ; ) seperated or comma seperated ( , ). A semicolon generally represents another column.

wezred
07-24-2005, 09:08 PM
Thanks for the tips so far. It really is useful to learn about what we can do with arrays.:beerchug:

As for the solution about, I realised that it only works with 1 "transpose" variable. I'm trying to make it work with about 5 "transpose" variable. Please see attached file. You can see that it says #N/A if I tried to do this with 2 variables.


=SUM(IF(($G$1:$G$13=TRANSPOSE((IF($A$1:$A$5=1,$B$1:$B$5))))*($H$1:$H$13=TRA NSPOSE((IF($D$1:$D$3=1,$E$1:$E$3)))),($I$1:$I$13)))

This formula doesn't work!! :dunno
Any solution is really appreciated.

Thank you.:thumb

acw
07-24-2005, 10:20 PM
Hi

If the answer is supposed to be 14 then try


=SUM(IF(ISNA(IF(($G$1:$G$13=TRANSPOSE((IF($A$1:$A$5=1,$B$1:$B$5))))*($H$1:$ H$13=TRANSPOSE((IF($D$1:$D$3=1,$E$1:$E$3)))),($I$1:$I$13))*1),0,IF(($G$1:$G $13=TRANSPOSE((IF($A$1:$A$5=1,$B$1:$B$5))))*($H$1:$H$13=TRANSPOSE((IF($D$1: $D$3=1,$E$1:$E$3)))),($I$1:$I$13))*1))


Tony

wezred
07-24-2005, 11:09 PM
Nope, the answer is suppose to be 40.

Also, I'm trying to avoid really long formulas and avoid using the ISNA coding.

Your code only picks out the top cells for each "transpose" column.:banghead:

Bob Phillips
07-26-2005, 04:29 AM
Nope, the answer is suppose to be 40.

Also, I'm trying to avoid really long formulas and avoid using the ISNA coding.

Your code only picks out the top cells for each "transpose" column.:banghead:

Gotcha!


=SUMPRODUCT(--(ISNUMBER(MATCH(1&G1:G13,A1:A5&B1:B5,0))),--(ISNUMBER(MATCH(1&H1:H13,D1:D3&E1:E3,0))),I1:I13)

Bob Phillips
07-26-2005, 07:28 AM
Gotcha!

=SUMPRODUCT(--(ISNUMBER(MATCH(1&G1:G13,A1:A5&B1:B5,0))),--(ISNUMBER(MATCH(1&H1:H13,D1:D3&E1:E3,0))),I1:I13)

That stupid grinning face should be the colon symbol : and D

wezred
07-26-2005, 08:40 PM
Thanks for the help xld. Using the match formula is a great solution and it works!!

Going back to the main question of this topic. How do I use VBA "Function or Subs" to code an array should the formula be too long?

Thanks.

Bob Phillips
07-27-2005, 02:26 AM
Thanks for the help xld. Using the match formula is a great solution and it works!!

Going back to the main question of this topic. How do I use VBA "Function or Subs" to code an array should the formula be too long?

You pass the array(s) to the function as a range object, if in cells, or as a variant, if passing the values direct, and iterate through each item. You may be able to invoke some worksheetfunctions in isolation, but that would depend upon the problem.