Results 1 to 7 of 7
Thread: Need Array Formula (XL2K)

20030127, 21:19 #1
 Join Date
 Mar 2001
 Location
 Springfield, Ohio, USA
 Posts
 2,136
 Thanks
 0
 Thanked 2 Times in 2 Posts
Need Array Formula (XL2K)
Wavelengths in column A and error in column J
Min wavelength in U2
Max wavelength in V2
Need a formula to calculate STDEV of errors for wavelengths between Min and Max
Also need a formula to calculate RMS of errors (SQRT(SUMSQ / COUNT) between Min and Max
But if there are 9 or less wavelengths that have an error value, result is #N/A (sometimes there hardly any good data in that range.)
TIA Sam<font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
<small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

20030128, 00:03 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Need Array Formula (XL2K)
Assume the errors are in a range named Errors:
Std Dev of errors is given by the ARRAY formula (ctrlshiftenter to confirm, though I assume you know this and this is for the other readers who do NOT):
=STDEV(IF(ISNUMBER(Errors),Errors))
RMS of errors =(SQRT(SUMSQ / COUNT) can be obtained by ARRAY formula:
=SQRT(SUM(IF(ISNUMBER(Errors),Errors^2))/COUNT(IF(ISNUMBER(Errors),Errors)))
I wasn't sure but were you actually looking for getting the errors within a particular range (>=U2 and <=V2)?
These would be the ARRAYs (Wavelength is the named range for the wavelengths), I assume there are no N/As in the wavelength column, otherwise you will need to modify with an IF to check for isnumber or the >= and <= will yield #N/A.
=STDEV(IF((ISNUMBER(Errors))*(Wavelength>=$u$2)*(W avelength<=$v$2),Errors))
and for RMSall one line):
=SQRT(SUM(IF((ISNUMBER(Errors))*(Wavelength>=$u$2) *(Wavelength<=$v$2),Errors^2))/COUNT(IF((ISNUMBER(Errors))*(Wavelength>=$u$2)*(Wa velength<=$v$2),Errors)))
Steve

20030128, 03:52 #3
 Join Date
 Mar 2001
 Location
 Springfield, Ohio, USA
 Posts
 2,136
 Thanks
 0
 Thanked 2 Times in 2 Posts
Re: Need Array Formula (XL2K)
But, you only did half of the problem:
=STDEV(IF((ISNUMBER(Errors))*(Wavelength>=$u$2)*(W avelength<=$v$2),Errors))
is the formula I need if there are more than 9 entries in the Error range being considered, but I want the formula to return #N/A if there are less than 9 error entries.
Also, why does this modification of your formula return #NUM!
{=STDEV(IF((ISNUMBER($A:$A))*(ISNUMBER($J:$J))*($A :$A>=$U11)*($A:$A<=$V11),$J:$J))}
The resolution of the data changes from experiment to experiment, so I will have to change the definition of the named ranges to use your formula. Looking at the entire column would be nicer.<font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
<small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

20030128, 04:19 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Need Array Formula (XL2K)
I dindn't get that in your original note. I thought you meant you were getting NAs when you had less than 10. Here is an Array formula to answer that question:
=IF(COUNT(IF(ISNUMBER(Errors),Errors))<10,NA(),STD EV(IF(ISNUMBER(Errors),Errors)))
You can NOT use an array formula with an ENTIRE column. It is a "feature" to prevent people from making HUGE arrays. You can use:
{=STDEV(IF((ISNUMBER($A1:$A65535))*(ISNUMBER($J1:$ J65535))*($A1:$A65535>=$U11)*($A1:$A65535<=$V11),$ J1:$J65535))}
[Why 1 less is OK is beyond me!], or use some other more reasonable maximum.
You could also go with naming the ranges, perhaps using OFFSET in a range name, to make the range variable and shrink and grow with the data.
Note: in your formula, the Isnumber("A") will NOT prevent NAs (and other error results) from creeping into the result. If any cells in A are NA then the "A">=U11 and "A"<=V11 will yield NAs instead of a True/False. You need another IF:
{=STDEV(IF((ISNUMBER($A1:$A65535)),if((ISNUMBER($J 1:$J65535))*($A1:$A65535>=$U11)*($A1:$A65535<=$V11 ),$J1:$J65535)))}
to prevent the errors from coming from the comparisons.
Steve

20030128, 04:26 #5
 Join Date
 Mar 2001
 Location
 Springfield, Ohio, USA
 Posts
 2,136
 Thanks
 0
 Thanked 2 Times in 2 Posts
Re: Need Array Formula (XL2K)
The entire column not allowed feature is bizzare, glad that you documented and confirmed it. The best answer based on all of your input is
=IF(COUNT(IF((ISNUMBER(Es))*(Ws>$U19)*(Ws<$V19),Es ))>9,STDEV(IF((ISNUMBER(Es))*(Ws>$U19)*(Ws<$V19),E s)),NA())
and a similar line for RMS. I shortened the names from Wavelengths to Ws and Errors to Es so that they would fit on a line. Thanks!<font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
<small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

20030128, 04:52 #6
 Join Date
 Mar 2001
 Location
 Springfield, Ohio, USA
 Posts
 2,136
 Thanks
 0
 Thanked 2 Times in 2 Posts
Re: Need Array Formula (XL2K)
<img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> Why did I get myself into an array formula mindset? As the attached worksheet shows, if I just add another column and use the MATCH function to find the desired range, then I can use that range with the INDIRECT function. Simple and I can use the entire column in the match function. I could have used the MATCH & the INDIRECT function and skipped the extra column, but I like to take small steps. Guess that's why I hate array formulas. Thanks again, Steve!
<font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
<small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

20030128, 11:48 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Need Array Formula (XL2K)
I have found that all array formulas and all megaformulas can be done using extra columns and smaller (generally more straightforward formulas and calcs).
Arrays can clean up the spreadsheet (though intermediate columns may be hidden) but also make the need for some intermediate formulas to not have to be copied when new data is added. so can help eliminate the need for some macro code, making an "allformula" based workbook, which many people like because of macro warnings.
Steve