# Thread: Need Array Formula (XL2K)

1. ## 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

2. ## Re: Need Array Formula (XL2K)

Assume the errors are in a range named Errors:

Std Dev of errors is given by the ARRAY formula (ctrl-shift-enter 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

3. ## 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.

4. ## 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

5. ## 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!

6. ## 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!

7. ## 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 "all-formula" based workbook, which many people like because of macro warnings.

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•