Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

{1;2;3;4;5;6;7;8;9;10} to {1;2;0;0;0;6;7;0;0;10}

Posted by Somkiat on December 26, 2001 12:14 AM
Cell A1:A10 has number 1:10
B1 = 3
C1 = 5
B2 = 8
C2 = 9

I use this array formula

{=IF(((A1:A10<B1)+(A1:A10>C1))*((A1:A10<B2)+(A1:A10>C2)),A1:A10,0)}

to get {1;2;0;0;0;6;7;0;0;10}

which makes number 3 to 5 and number 8 to 9 to be 0 instead.

Is there any shorter array formula?

My real situation has number from 150 to 25000 that need to replace number from 200-250, 400-570, 15000-16000, 21200-23400 to be 0. Interval of 0 may be change and/or increase/decrease in future.

I understand deeply enough for array and vba. Please advise array formula.

Thanks,
Somkiat


Check out our Excel Resources

A revised formula

Posted by Somkiat on December 26, 2001 12:20 AM
Symbol of less than and greater than has been deleted from my post. So this is my formula :

{=IF(((A1:A10 less than B1)+(A1:A10 greater than C1))*((A1:A10 less than B2)+(A1:A10 greater than C2)),A1:A10,0)}

to get {1;2;0;0;0;6;7;0;0;10}


Re: A revised formula

Posted by Juan Pablo G. on December 26, 2001 6:58 AM
This is basically the same, but maybe will work a little faster.

=((A1:A10 < B1)+(A1:A10 > C1))*((A1:A10 < B2)+(A1:A10 > C2))*A1:A10

Juan Pablo G.


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.