Find out index of values within a certain range in Excel
I have a table of values in Excel (2103 if that makes a difference), which when plotted looks something like that:
I am trying to find for each series the index of the value, after which point all subsequent values lie within a certain range (between 99 and 101 in that case). Note that the values are not necessarily in ascending or descending order, but they will all eventually converge towards the same value (100 in that case).
I have done some searching and it feels like some combination of MATCH, IF and/or VLOOKUP should do it, but I can't figure it out. Can anybody help?
Thanks!
microsoft-excel worksheet-function vlookup
add a comment |
I have a table of values in Excel (2103 if that makes a difference), which when plotted looks something like that:
I am trying to find for each series the index of the value, after which point all subsequent values lie within a certain range (between 99 and 101 in that case). Note that the values are not necessarily in ascending or descending order, but they will all eventually converge towards the same value (100 in that case).
I have done some searching and it feels like some combination of MATCH, IF and/or VLOOKUP should do it, but I can't figure it out. Can anybody help?
Thanks!
microsoft-excel worksheet-function vlookup
as much as your data can fluctuate, will it always converge from the low side of the convergence line?
– Forward Ed
Jan 24 at 13:02
Are data points equally spaced along the x axis?
– Forward Ed
Jan 24 at 13:02
Yes, it's a "step response" so the data will always converge from the low side, but it may overshoot. And yes, the data is equally spaced along the x-axis.
– am304
Jan 24 at 13:03
add a comment |
I have a table of values in Excel (2103 if that makes a difference), which when plotted looks something like that:
I am trying to find for each series the index of the value, after which point all subsequent values lie within a certain range (between 99 and 101 in that case). Note that the values are not necessarily in ascending or descending order, but they will all eventually converge towards the same value (100 in that case).
I have done some searching and it feels like some combination of MATCH, IF and/or VLOOKUP should do it, but I can't figure it out. Can anybody help?
Thanks!
microsoft-excel worksheet-function vlookup
I have a table of values in Excel (2103 if that makes a difference), which when plotted looks something like that:
I am trying to find for each series the index of the value, after which point all subsequent values lie within a certain range (between 99 and 101 in that case). Note that the values are not necessarily in ascending or descending order, but they will all eventually converge towards the same value (100 in that case).
I have done some searching and it feels like some combination of MATCH, IF and/or VLOOKUP should do it, but I can't figure it out. Can anybody help?
Thanks!
microsoft-excel worksheet-function vlookup
microsoft-excel worksheet-function vlookup
asked Jan 24 at 11:31
am304am304
15019
15019
as much as your data can fluctuate, will it always converge from the low side of the convergence line?
– Forward Ed
Jan 24 at 13:02
Are data points equally spaced along the x axis?
– Forward Ed
Jan 24 at 13:02
Yes, it's a "step response" so the data will always converge from the low side, but it may overshoot. And yes, the data is equally spaced along the x-axis.
– am304
Jan 24 at 13:03
add a comment |
as much as your data can fluctuate, will it always converge from the low side of the convergence line?
– Forward Ed
Jan 24 at 13:02
Are data points equally spaced along the x axis?
– Forward Ed
Jan 24 at 13:02
Yes, it's a "step response" so the data will always converge from the low side, but it may overshoot. And yes, the data is equally spaced along the x-axis.
– am304
Jan 24 at 13:03
as much as your data can fluctuate, will it always converge from the low side of the convergence line?
– Forward Ed
Jan 24 at 13:02
as much as your data can fluctuate, will it always converge from the low side of the convergence line?
– Forward Ed
Jan 24 at 13:02
Are data points equally spaced along the x axis?
– Forward Ed
Jan 24 at 13:02
Are data points equally spaced along the x axis?
– Forward Ed
Jan 24 at 13:02
Yes, it's a "step response" so the data will always converge from the low side, but it may overshoot. And yes, the data is equally spaced along the x-axis.
– am304
Jan 24 at 13:03
Yes, it's a "step response" so the data will always converge from the low side, but it may overshoot. And yes, the data is equally spaced along the x-axis.
– am304
Jan 24 at 13:03
add a comment |
1 Answer
1
active
oldest
votes
A couple of possible approaches:
OPTION 1
1) Sort your data based on the X value and sort in descending order. You want your largest X value to be your first point.
2) Determine the slope of the line between each subsequent point. In an adjacent column.
3) When the slope of the line is calculated but is greater than the max value for point 1 and min value for point two (or vice versa), you know your are beyond your convergence point.
OPTION 2
1) Sort your data based on the X value in descending order. same as option1
2) Assuming you have a way of determining the convergence value put that in a cell.
3) Find the first Y value in the list that exceeds the convergence value by + or - your tolerance.
FINDING FIRST OCCURRENCE
In order to find the first occurrence of something, you will want to use either an array formula, or a formula that performs array-like calculations. I will attempt to illustrate using the AGGREGATE function, but there are other methods. Since AGGREGATE performs array calculation for the options I am choosing, avoid using full column references within the AGGREGATE function or excessive calculations may be made that could slow down your system.
AGGGREGATE (Function #, Error/Hidden Options, Your formula, Parameter)
The above is the basic break down of the AGGREGATE function. What we are going to do is tell AGGREGATE to sort the results from our formula in ascending order, ignore any formula results that are errors, and then give us the first entry in the sorted list. Believe it or not, most of that is pretty straight forward. I'll leave that for now, and focus on the hard part...our formula.
What we are looking for is the first occurrence of something. We are either going to need the value of that something or its position in a list (or row number). Normally in this situation, the thing I am trying to find I usually put as the numerator in the formula. Any conditions that need to be met that give TRUE or FALSE results get placed in the denominator of the formula.
The trick here is that when TRUE or FALSE are placed through a math operation, they are converted to 1 for TRUE and 0 for FALSE. (As a total aside, when functions a like IF are looking for TRUE and FALSE in their decision making, 0 is treated as FALSE and EVERY OTHER NUMBER IS TRUE.) So any time your divide by FALSE, you will be dividing by 0, which causes an error for the calculation, which was previously stated that AGGREGATE would be set to ignore.
So with proper formula building you will get a sorted list of items that only match your conditions in the denominator. And since its a sorted list in Ascending order, when its set to return the 1st entry, you will get (in our case) the lowest row number, or least value.
Example: Lets assume your X values are in column B and your Y values are in column C and your data is in rows 2:100. We want to find the first Y value that is more than 2.4 away from 81.3
=AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-81.3)>=2.4),1)
Now to return the value of that cell, you would embed the AGGREGATE formula in an INDEX formula.
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-81.3)>=2.4),1))
Notice that the full column reference of B:B is outside the AGGREGATE function, which is OK. IF you want to know what Y value this is, change the B:B to C:C.
Based on your criteria of being between 101 and 99, you could use:
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-100)>=1),1))
OR
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(($C$2:$C$100>=101)*($C$2:$C$100<=99)),1))
Those equations will give you the value of the first point outside your limits. So to get the last point from inside your limits, you need to subtract 1 from the row number which gives a new formula of:
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-100)>=1),1)-1)
OR
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(($C$2:$C$100>=101)*($C$2:$C$100<=99)),1)-1)
Note the *
between the two condition checks in the denominator. The *
acts like an AND function (only 1 * 1 = 1; both conditions must be true). A +
would act like an OR function (either or both can be true for the result to be non-zero).
Thanks, in the end I did it "by hand" as I didn't have that much data to go through, although I can see this approach being useful in the future if I have to do the same thing again on a larger dataset.
– am304
Feb 11 at 15:03
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1397877%2ffind-out-index-of-values-within-a-certain-range-in-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
A couple of possible approaches:
OPTION 1
1) Sort your data based on the X value and sort in descending order. You want your largest X value to be your first point.
2) Determine the slope of the line between each subsequent point. In an adjacent column.
3) When the slope of the line is calculated but is greater than the max value for point 1 and min value for point two (or vice versa), you know your are beyond your convergence point.
OPTION 2
1) Sort your data based on the X value in descending order. same as option1
2) Assuming you have a way of determining the convergence value put that in a cell.
3) Find the first Y value in the list that exceeds the convergence value by + or - your tolerance.
FINDING FIRST OCCURRENCE
In order to find the first occurrence of something, you will want to use either an array formula, or a formula that performs array-like calculations. I will attempt to illustrate using the AGGREGATE function, but there are other methods. Since AGGREGATE performs array calculation for the options I am choosing, avoid using full column references within the AGGREGATE function or excessive calculations may be made that could slow down your system.
AGGGREGATE (Function #, Error/Hidden Options, Your formula, Parameter)
The above is the basic break down of the AGGREGATE function. What we are going to do is tell AGGREGATE to sort the results from our formula in ascending order, ignore any formula results that are errors, and then give us the first entry in the sorted list. Believe it or not, most of that is pretty straight forward. I'll leave that for now, and focus on the hard part...our formula.
What we are looking for is the first occurrence of something. We are either going to need the value of that something or its position in a list (or row number). Normally in this situation, the thing I am trying to find I usually put as the numerator in the formula. Any conditions that need to be met that give TRUE or FALSE results get placed in the denominator of the formula.
The trick here is that when TRUE or FALSE are placed through a math operation, they are converted to 1 for TRUE and 0 for FALSE. (As a total aside, when functions a like IF are looking for TRUE and FALSE in their decision making, 0 is treated as FALSE and EVERY OTHER NUMBER IS TRUE.) So any time your divide by FALSE, you will be dividing by 0, which causes an error for the calculation, which was previously stated that AGGREGATE would be set to ignore.
So with proper formula building you will get a sorted list of items that only match your conditions in the denominator. And since its a sorted list in Ascending order, when its set to return the 1st entry, you will get (in our case) the lowest row number, or least value.
Example: Lets assume your X values are in column B and your Y values are in column C and your data is in rows 2:100. We want to find the first Y value that is more than 2.4 away from 81.3
=AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-81.3)>=2.4),1)
Now to return the value of that cell, you would embed the AGGREGATE formula in an INDEX formula.
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-81.3)>=2.4),1))
Notice that the full column reference of B:B is outside the AGGREGATE function, which is OK. IF you want to know what Y value this is, change the B:B to C:C.
Based on your criteria of being between 101 and 99, you could use:
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-100)>=1),1))
OR
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(($C$2:$C$100>=101)*($C$2:$C$100<=99)),1))
Those equations will give you the value of the first point outside your limits. So to get the last point from inside your limits, you need to subtract 1 from the row number which gives a new formula of:
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-100)>=1),1)-1)
OR
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(($C$2:$C$100>=101)*($C$2:$C$100<=99)),1)-1)
Note the *
between the two condition checks in the denominator. The *
acts like an AND function (only 1 * 1 = 1; both conditions must be true). A +
would act like an OR function (either or both can be true for the result to be non-zero).
Thanks, in the end I did it "by hand" as I didn't have that much data to go through, although I can see this approach being useful in the future if I have to do the same thing again on a larger dataset.
– am304
Feb 11 at 15:03
add a comment |
A couple of possible approaches:
OPTION 1
1) Sort your data based on the X value and sort in descending order. You want your largest X value to be your first point.
2) Determine the slope of the line between each subsequent point. In an adjacent column.
3) When the slope of the line is calculated but is greater than the max value for point 1 and min value for point two (or vice versa), you know your are beyond your convergence point.
OPTION 2
1) Sort your data based on the X value in descending order. same as option1
2) Assuming you have a way of determining the convergence value put that in a cell.
3) Find the first Y value in the list that exceeds the convergence value by + or - your tolerance.
FINDING FIRST OCCURRENCE
In order to find the first occurrence of something, you will want to use either an array formula, or a formula that performs array-like calculations. I will attempt to illustrate using the AGGREGATE function, but there are other methods. Since AGGREGATE performs array calculation for the options I am choosing, avoid using full column references within the AGGREGATE function or excessive calculations may be made that could slow down your system.
AGGGREGATE (Function #, Error/Hidden Options, Your formula, Parameter)
The above is the basic break down of the AGGREGATE function. What we are going to do is tell AGGREGATE to sort the results from our formula in ascending order, ignore any formula results that are errors, and then give us the first entry in the sorted list. Believe it or not, most of that is pretty straight forward. I'll leave that for now, and focus on the hard part...our formula.
What we are looking for is the first occurrence of something. We are either going to need the value of that something or its position in a list (or row number). Normally in this situation, the thing I am trying to find I usually put as the numerator in the formula. Any conditions that need to be met that give TRUE or FALSE results get placed in the denominator of the formula.
The trick here is that when TRUE or FALSE are placed through a math operation, they are converted to 1 for TRUE and 0 for FALSE. (As a total aside, when functions a like IF are looking for TRUE and FALSE in their decision making, 0 is treated as FALSE and EVERY OTHER NUMBER IS TRUE.) So any time your divide by FALSE, you will be dividing by 0, which causes an error for the calculation, which was previously stated that AGGREGATE would be set to ignore.
So with proper formula building you will get a sorted list of items that only match your conditions in the denominator. And since its a sorted list in Ascending order, when its set to return the 1st entry, you will get (in our case) the lowest row number, or least value.
Example: Lets assume your X values are in column B and your Y values are in column C and your data is in rows 2:100. We want to find the first Y value that is more than 2.4 away from 81.3
=AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-81.3)>=2.4),1)
Now to return the value of that cell, you would embed the AGGREGATE formula in an INDEX formula.
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-81.3)>=2.4),1))
Notice that the full column reference of B:B is outside the AGGREGATE function, which is OK. IF you want to know what Y value this is, change the B:B to C:C.
Based on your criteria of being between 101 and 99, you could use:
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-100)>=1),1))
OR
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(($C$2:$C$100>=101)*($C$2:$C$100<=99)),1))
Those equations will give you the value of the first point outside your limits. So to get the last point from inside your limits, you need to subtract 1 from the row number which gives a new formula of:
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-100)>=1),1)-1)
OR
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(($C$2:$C$100>=101)*($C$2:$C$100<=99)),1)-1)
Note the *
between the two condition checks in the denominator. The *
acts like an AND function (only 1 * 1 = 1; both conditions must be true). A +
would act like an OR function (either or both can be true for the result to be non-zero).
Thanks, in the end I did it "by hand" as I didn't have that much data to go through, although I can see this approach being useful in the future if I have to do the same thing again on a larger dataset.
– am304
Feb 11 at 15:03
add a comment |
A couple of possible approaches:
OPTION 1
1) Sort your data based on the X value and sort in descending order. You want your largest X value to be your first point.
2) Determine the slope of the line between each subsequent point. In an adjacent column.
3) When the slope of the line is calculated but is greater than the max value for point 1 and min value for point two (or vice versa), you know your are beyond your convergence point.
OPTION 2
1) Sort your data based on the X value in descending order. same as option1
2) Assuming you have a way of determining the convergence value put that in a cell.
3) Find the first Y value in the list that exceeds the convergence value by + or - your tolerance.
FINDING FIRST OCCURRENCE
In order to find the first occurrence of something, you will want to use either an array formula, or a formula that performs array-like calculations. I will attempt to illustrate using the AGGREGATE function, but there are other methods. Since AGGREGATE performs array calculation for the options I am choosing, avoid using full column references within the AGGREGATE function or excessive calculations may be made that could slow down your system.
AGGGREGATE (Function #, Error/Hidden Options, Your formula, Parameter)
The above is the basic break down of the AGGREGATE function. What we are going to do is tell AGGREGATE to sort the results from our formula in ascending order, ignore any formula results that are errors, and then give us the first entry in the sorted list. Believe it or not, most of that is pretty straight forward. I'll leave that for now, and focus on the hard part...our formula.
What we are looking for is the first occurrence of something. We are either going to need the value of that something or its position in a list (or row number). Normally in this situation, the thing I am trying to find I usually put as the numerator in the formula. Any conditions that need to be met that give TRUE or FALSE results get placed in the denominator of the formula.
The trick here is that when TRUE or FALSE are placed through a math operation, they are converted to 1 for TRUE and 0 for FALSE. (As a total aside, when functions a like IF are looking for TRUE and FALSE in their decision making, 0 is treated as FALSE and EVERY OTHER NUMBER IS TRUE.) So any time your divide by FALSE, you will be dividing by 0, which causes an error for the calculation, which was previously stated that AGGREGATE would be set to ignore.
So with proper formula building you will get a sorted list of items that only match your conditions in the denominator. And since its a sorted list in Ascending order, when its set to return the 1st entry, you will get (in our case) the lowest row number, or least value.
Example: Lets assume your X values are in column B and your Y values are in column C and your data is in rows 2:100. We want to find the first Y value that is more than 2.4 away from 81.3
=AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-81.3)>=2.4),1)
Now to return the value of that cell, you would embed the AGGREGATE formula in an INDEX formula.
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-81.3)>=2.4),1))
Notice that the full column reference of B:B is outside the AGGREGATE function, which is OK. IF you want to know what Y value this is, change the B:B to C:C.
Based on your criteria of being between 101 and 99, you could use:
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-100)>=1),1))
OR
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(($C$2:$C$100>=101)*($C$2:$C$100<=99)),1))
Those equations will give you the value of the first point outside your limits. So to get the last point from inside your limits, you need to subtract 1 from the row number which gives a new formula of:
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-100)>=1),1)-1)
OR
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(($C$2:$C$100>=101)*($C$2:$C$100<=99)),1)-1)
Note the *
between the two condition checks in the denominator. The *
acts like an AND function (only 1 * 1 = 1; both conditions must be true). A +
would act like an OR function (either or both can be true for the result to be non-zero).
A couple of possible approaches:
OPTION 1
1) Sort your data based on the X value and sort in descending order. You want your largest X value to be your first point.
2) Determine the slope of the line between each subsequent point. In an adjacent column.
3) When the slope of the line is calculated but is greater than the max value for point 1 and min value for point two (or vice versa), you know your are beyond your convergence point.
OPTION 2
1) Sort your data based on the X value in descending order. same as option1
2) Assuming you have a way of determining the convergence value put that in a cell.
3) Find the first Y value in the list that exceeds the convergence value by + or - your tolerance.
FINDING FIRST OCCURRENCE
In order to find the first occurrence of something, you will want to use either an array formula, or a formula that performs array-like calculations. I will attempt to illustrate using the AGGREGATE function, but there are other methods. Since AGGREGATE performs array calculation for the options I am choosing, avoid using full column references within the AGGREGATE function or excessive calculations may be made that could slow down your system.
AGGGREGATE (Function #, Error/Hidden Options, Your formula, Parameter)
The above is the basic break down of the AGGREGATE function. What we are going to do is tell AGGREGATE to sort the results from our formula in ascending order, ignore any formula results that are errors, and then give us the first entry in the sorted list. Believe it or not, most of that is pretty straight forward. I'll leave that for now, and focus on the hard part...our formula.
What we are looking for is the first occurrence of something. We are either going to need the value of that something or its position in a list (or row number). Normally in this situation, the thing I am trying to find I usually put as the numerator in the formula. Any conditions that need to be met that give TRUE or FALSE results get placed in the denominator of the formula.
The trick here is that when TRUE or FALSE are placed through a math operation, they are converted to 1 for TRUE and 0 for FALSE. (As a total aside, when functions a like IF are looking for TRUE and FALSE in their decision making, 0 is treated as FALSE and EVERY OTHER NUMBER IS TRUE.) So any time your divide by FALSE, you will be dividing by 0, which causes an error for the calculation, which was previously stated that AGGREGATE would be set to ignore.
So with proper formula building you will get a sorted list of items that only match your conditions in the denominator. And since its a sorted list in Ascending order, when its set to return the 1st entry, you will get (in our case) the lowest row number, or least value.
Example: Lets assume your X values are in column B and your Y values are in column C and your data is in rows 2:100. We want to find the first Y value that is more than 2.4 away from 81.3
=AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-81.3)>=2.4),1)
Now to return the value of that cell, you would embed the AGGREGATE formula in an INDEX formula.
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-81.3)>=2.4),1))
Notice that the full column reference of B:B is outside the AGGREGATE function, which is OK. IF you want to know what Y value this is, change the B:B to C:C.
Based on your criteria of being between 101 and 99, you could use:
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-100)>=1),1))
OR
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(($C$2:$C$100>=101)*($C$2:$C$100<=99)),1))
Those equations will give you the value of the first point outside your limits. So to get the last point from inside your limits, you need to subtract 1 from the row number which gives a new formula of:
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-100)>=1),1)-1)
OR
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(($C$2:$C$100>=101)*($C$2:$C$100<=99)),1)-1)
Note the *
between the two condition checks in the denominator. The *
acts like an AND function (only 1 * 1 = 1; both conditions must be true). A +
would act like an OR function (either or both can be true for the result to be non-zero).
edited Jan 24 at 18:38
answered Jan 24 at 13:12
Forward EdForward Ed
1,006214
1,006214
Thanks, in the end I did it "by hand" as I didn't have that much data to go through, although I can see this approach being useful in the future if I have to do the same thing again on a larger dataset.
– am304
Feb 11 at 15:03
add a comment |
Thanks, in the end I did it "by hand" as I didn't have that much data to go through, although I can see this approach being useful in the future if I have to do the same thing again on a larger dataset.
– am304
Feb 11 at 15:03
Thanks, in the end I did it "by hand" as I didn't have that much data to go through, although I can see this approach being useful in the future if I have to do the same thing again on a larger dataset.
– am304
Feb 11 at 15:03
Thanks, in the end I did it "by hand" as I didn't have that much data to go through, although I can see this approach being useful in the future if I have to do the same thing again on a larger dataset.
– am304
Feb 11 at 15:03
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1397877%2ffind-out-index-of-values-within-a-certain-range-in-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
as much as your data can fluctuate, will it always converge from the low side of the convergence line?
– Forward Ed
Jan 24 at 13:02
Are data points equally spaced along the x axis?
– Forward Ed
Jan 24 at 13:02
Yes, it's a "step response" so the data will always converge from the low side, but it may overshoot. And yes, the data is equally spaced along the x-axis.
– am304
Jan 24 at 13:03