mishelper,
You need to be using an array formula to do what you request:
=CORREL(IF(U$16:U$2741=1,A
Entering an array formula is a little tricky: you need to hold the Control and Shift keys down, then hit Enter. Excel should respond by adding curly braces surrounding your entire formula. If not, select the cell, click in the formula bar and Control + Shift + Enter once again.
After some testing, I found that you do not need the IF in the second array. Only corresponding values will be considered.
To incorporate this formula into your VBA macro, you need to set the FormulaArray property instead of the Formula property:
Range("A1").FormulaArray = "=CORREL(IF(U$16:U$2741=1,
Brad
Main Topics
Browse All Topics
by: matthewspatrickPosted on 2009-11-01 at 04:18:53ID: 25713470
Hello mishelper,
It would be useful to see some sample data and/or a sample file. EE now allows you to directly upload files
to your question.
Please be advised that once you upload a file, it can be publicly accessed, and that it may not be possible
to fully and permanently delete it. Therefore, be very careful about posting proprietary, confidential, or
other sensitive information. If necessary, use "fake" and/or obfuscated data in your sample.
Please note that at present EE restricts uploads to certain file types. If your file type does not match
those in the list, you can use http://www.ee-stuff.com instead, which is not officially an EE site, but is run
by people connected to EE.
Regards,
Patrick