| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array function that returns values within several intervals
Hi,
I need to subtract numbers in a column (A2:A4) from the numbers in a row (B2:D2) 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(B1:D1-A2:A4F1,B1:D1-A2:A4<F2),AND(B1:D1-A2:A4F3,B1:D1-A2:A4<F4)),B1:D1-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 :) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array function that returns values within several intervals
Try this
=IF(OR((SUM(B1:D1)-SUM(A2:A4))F1,(SUM(B1:D1)-SUM(A2:A4))<F2, (SUM(B1:D1)-SUM(A2:A4))F3,(SUM(B1:D1)-SUM(A2:A4)<F4)),SUM((B1:D1))- SUM((A2:A4)),"-") On Sep 23, 12:38*pm, Hjuler wrote: Hi, I need to subtract numbers in a column (A2:A4) from the numbers in a row (B2:D2) 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(B1:D1-A2:A4F1,B1:D1-A2:A4<F2),AND(B1:D1-A2:A4F3,B1:D1-A2:A4<F4)),B1:D1-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 :) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array function that returns values within several intervals
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: Try this =IF(OR((SUM(B1:D1)-SUM(A2:A4))F1,(SUM(B1:D1)-SUM(A2:A4))<F2, (SUM(B1:D1)-SUM(A2:A4))F3,(SUM(B1:D1)-SUM(A2:A4)<F4)),SUM((B1:D1))- SUM((A2:A4)),"-") On Sep 23, 12:38 pm, Hjuler wrote: Hi, I need to subtract numbers in a column (A2:A4) from the numbers in a row (B2:D2) 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(B1:D1-A2:A4F1,B1:D1-A2:A4<F2),AND(B1:D1-A2:A4F3,B1:D1-A2:A4<F4)),B1:D1-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 :) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array function that returns values within several intervals
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. -- David Biddulph "Hjuler" wrote in message ... 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: Try this =IF(OR((SUM(B1:D1)-SUM(A2:A4))F1,(SUM(B1:D1)-SUM(A2:A4))<F2, (SUM(B1:D1)-SUM(A2:A4))F3,(SUM(B1:D1)-SUM(A2:A4)<F4)),SUM((B1:D1))- SUM((A2:A4)),"-") On Sep 23, 12:38 pm, Hjuler wrote: Hi, I need to subtract numbers in a column (A2:A4) from the numbers in a row (B2:D2) 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(B1:D1-A2:A4F1,B1:D1-A2:A4<F2),AND(B1:D1-A2:A4F3,B1:D1-A2:A4<F4)),B1:D1-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 :) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array function that returns values within several intervals
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: 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. -- David Biddulph "Hjuler" wrote in message ... 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: Try this =IF(OR((SUM(B1:D1)-SUM(A2:A4))F1,(SUM(B1:D1)-SUM(A2:A4))<F2, (SUM(B1:D1)-SUM(A2:A4))F3,(SUM(B1:D1)-SUM(A2:A4)<F4)),SUM((B1:D1))- SUM((A2:A4)),"-") On Sep 23, 12:38 pm, Hjuler wrote: Hi, I need to subtract numbers in a column (A2:A4) from the numbers in a row (B2:D2) 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(B1:D1-A2:A4F1,B1:D1-A2:A4<F2),AND(B1:D1-A2:A4F3,B1:D1-A2:A4<F4)),B1:D1-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 :) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array function that returns values within several intervals
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? -- David Biddulph "Hjuler" wrote in message ... 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: 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. -- David Biddulph "Hjuler" wrote in message ... 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: Try this =IF(OR((SUM(B1:D1)-SUM(A2:A4))F1,(SUM(B1:D1)-SUM(A2:A4))<F2, (SUM(B1:D1)-SUM(A2:A4))F3,(SUM(B1:D1)-SUM(A2:A4)<F4)),SUM((B1:D1))- SUM((A2:A4)),"-") On Sep 23, 12:38 pm, Hjuler wrote: Hi, I need to subtract numbers in a column (A2:A4) from the numbers in a row (B2:D2) 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(B1:D1-A2:A4F1,B1:D1-A2:A4<F2),AND(B1:D1-A2:A4F3,B1:D1-A2:A4<F4)),B1:D1-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 :) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array function that returns values within several intervals
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: 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? -- David Biddulph "Hjuler" wrote in message ... 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: 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. -- David Biddulph "Hjuler" wrote in message ... 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: Try this =IF(OR((SUM(B1:D1)-SUM(A2:A4))F1,(SUM(B1:D1)-SUM(A2:A4))<F2, (SUM(B1:D1)-SUM(A2:A4))F3,(SUM(B1:D1)-SUM(A2:A4)<F4)),SUM((B1:D1))- SUM((A2:A4)),"-") On Sep 23, 12:38 pm, Hjuler wrote: Hi, I need to subtract numbers in a column (A2:A4) from the numbers in a row (B2:D2) 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(B1:D1-A2:A4F1,B1:D1-A2:A4<F2),AND(B1:D1-A2:A4F3,B1:D1-A2:A4<F4)),B1:D1-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 :) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using Match function with duplicate values in an array | Excel Worksheet Functions | |||
| Worksheet Function to Create Array of Size n with values x Through | Excel Discussion (Misc queries) | |||
| sum up values at intervals | Excel Discussion (Misc queries) | |||
| Non-Array Function result in non-accurate values | Excel Discussion (Misc queries) | |||
| Non-Array Function result in non-accurate values | Excel Worksheet Functions | |||
Linear Mode
