Array function that returns values within several intervals

[Hjuler]

Hi,
I need to subtract numbers in a column (A2:A4) from the numbers in a row
(B22) in all possible combination, Find and return all values that match
certain intervals (F1,F2,F3,F4).

My array formula looks like this:
IF(OR(AND(B11-A2:A4>F1,B11-A2:A4<F2),AND(B11-A2:A4>F3,B11-A2:A4<F4)),B11-A2:A4,"-")

The problem is that the formula only returns "-" even though I know some of
the results are within the intervals (evaluates all the results as "not true"
even though I know they are true).

This is a simplyfied version of my work but it will illustrate my problem.
In reality I have more than 200 numbers in the row and the column and more
than 10 intervals...

I am working on a danish version of Excel so please tell me if I have
converted some symbols wrong

 

[muddan madhu]

Try this

=IF(OR((SUM(B11)-SUM(A2:A4))>F1,(SUM(B11)-SUM(A2:A4))<F2,
(SUM(B11)-SUM(A2:A4))>F3,(SUM(B11)-SUM(A2:A4)<F4)),SUM((B11))-
SUM((A2:A4)),"-")

 

[Hjuler]

Hi again,

this one is not working either. With the "SUM"-command it returns the same
value in all of the cells...

"muddan madhu" skrev:

 

[David Biddulph]

What do you mean by "in all of the cells"? You have one formula. My guess
is that you haven't really explained what you want. Try again to explain
your requirements. Give some examples if that helps.

 

[Hjuler]

Okay, I will try to explain further:
It is a multicell array formula (only one formula required). My sheet looks
like this:

2 4 8
1
3
5

The 3x3 square between the numbers are where the function should be entered.

I need the differences between values in the top row and values in the first
column, and only return the values that lies within predefined intervals.
Let's say I'm only interested in results that is larger than 2 but smaller
than 4.
Then, I would get this result:

2 4 8
1 3
3
5 3

I hope this clarifies my problem further.

"David Biddulph" skrev:

 

[David Biddulph]

You don't need an array formula, do you?
Isn't it as simple as
=IF(OR(AND(B$1-$A2>$F$1,B$1-$A2<$F$2),AND(B$1-$A2>$F$3,B$1-$A2<$F$4)),B$1-$A2,"-")
in B2, copied down and across as appropriate?

 

[Hjuler]

You are right, it can be done without an array formula. The thing is my work
is at an early stage and I will be doing a lot of editing until it is
finished. This is MUCH easier with array formulas. Also, the data I have now
will produce more than 65000 results! (and there is more to come). AFAIK,
array formulas are quicker when having that amount of data but I haven't
really worked that much with arrays. Am I wrong?

The copy-method really brings my brand new pc to its knees, but maybe that's
a Vista-Excel 2007 problem...

Anyway, I appreciate you time on this problem.

"David Biddulph" skrev: