TheMongoose
New Member
- Joined
- Jul 16, 2018
- Messages
- 2
Ok, so I've gotten a lot of use from this forum and now I have a question I haven't been able to find an answer to, I'm totally shocked by that fact...I'll probably learn I wasn't using the right search terms lol. Lots of detail here, possibly too much but hopefully better than not-enough 
Some background to kick this off: I have a set of data that when graphed looks like a bell curve but stretched out to the right side, so it has along tail. It represents the percent of a gas in an air sample. The percentage increases quickly and then slowly dissipates, getting back to within about 3% of the starting value.
What I want to know: Can I use the index/match function to find the 1st cell number where the value is 5% of the starting value or am I going to have to write some VBA code to do this?
Why i want to know: I have to give the Excel file to production to use and VBA code can make it overcomplicated. 2nd, I need to calculate the area under the curve but there are so many points in that tail that I want to limit the number I am using rather than waste calculation time.
What I know now:
1. Raw Data is in tab FC_dataCurves_bySN! There are 140 columns and 30,000 rows. Each row is a sample of interest.
2. mR! tab has the absolute value of the difference between successive data points. This is how I determine when the curve starts to increase rapidly toward the peak value.
3. Calculations are in a separate tab.
4. The curve has 3 distinct parts - the baseline where the value hardly moves at all, the peak, and the tail.
5. I determine the baseline by looking at the moving range - C2 is {=MATCH(TRUE,INDEX(mR!B2:EH2>0.002,0),)]} which gives me a column number. By default it's giving me the first instance of the value being higher than 0.002 then to get the actual value of the baseline I used =INDEX(FC_dataCurves_bySN!B2:EJ2,,C2)
6. The equations are
A2: a constant that represents the row number. I think I can use ROW() later in the equations instead, but this is where I started at.
C2: column for moving range {=MATCH(TRUE,INDEX(mR!B2:EH2>0.002,0),)]}
D2: value of moving range {=INDEX(mR!B2:EI2,MATCH(TRUE,INDEX(mR!B2:EI2>0.002,0),))}
E2: Baseline value =INDEX(FC_dataCurves_bySN!B2:EJ2,,C2)
F2: Peak =MAX(FC_dataCurves_bySN!B2:EJ2)
G2: Column ref for peak value =MATCH(TRUE,INDEX(FC_dataCurves_bySN!B2:EJ2=F2,0),)
L2: =SUBSTITUTE(ADDRESS(1,G2+1,4),"1",""). This gives the letter ref for the column, BC in this case. It's offset by "1" because the serial number is in the first column.
The hard part seems to be getting the column number for the tail (the point at which the gas has decayed to within 5% of the starting value). I can't use the same range for the array because the 1st value the index/match function finds is on the increasing side of the curve and I need the value on the declining side. I figured to use the peak as the starting point for my array but can't get the function to accept a variable input for the starting column. Here's what I wrote
{=MATCH(TRUE,INDEX(INDIRECT(CONCATENATE("FC_datacurves_bySN!",L2,A2)):EH2<0.3220,0),)}
The above gives a #value ! Error and when I look at the results of the individual parts the index array indicates volatile. Since my starting value for the array isnt constant I need a way to use a variable within the index/match array.
<colgroup><col span="19"><col></colgroup><tbody>
</tbody>
Some background to kick this off: I have a set of data that when graphed looks like a bell curve but stretched out to the right side, so it has along tail. It represents the percent of a gas in an air sample. The percentage increases quickly and then slowly dissipates, getting back to within about 3% of the starting value.
What I want to know: Can I use the index/match function to find the 1st cell number where the value is 5% of the starting value or am I going to have to write some VBA code to do this?
Why i want to know: I have to give the Excel file to production to use and VBA code can make it overcomplicated. 2nd, I need to calculate the area under the curve but there are so many points in that tail that I want to limit the number I am using rather than waste calculation time.
What I know now:
1. Raw Data is in tab FC_dataCurves_bySN! There are 140 columns and 30,000 rows. Each row is a sample of interest.
2. mR! tab has the absolute value of the difference between successive data points. This is how I determine when the curve starts to increase rapidly toward the peak value.
3. Calculations are in a separate tab.
4. The curve has 3 distinct parts - the baseline where the value hardly moves at all, the peak, and the tail.
5. I determine the baseline by looking at the moving range - C2 is {=MATCH(TRUE,INDEX(mR!B2:EH2>0.002,0),)]} which gives me a column number. By default it's giving me the first instance of the value being higher than 0.002 then to get the actual value of the baseline I used =INDEX(FC_dataCurves_bySN!B2:EJ2,,C2)
6. The equations are
A2: a constant that represents the row number. I think I can use ROW() later in the equations instead, but this is where I started at.
C2: column for moving range {=MATCH(TRUE,INDEX(mR!B2:EH2>0.002,0),)]}
D2: value of moving range {=INDEX(mR!B2:EI2,MATCH(TRUE,INDEX(mR!B2:EI2>0.002,0),))}
E2: Baseline value =INDEX(FC_dataCurves_bySN!B2:EJ2,,C2)
F2: Peak =MAX(FC_dataCurves_bySN!B2:EJ2)
G2: Column ref for peak value =MATCH(TRUE,INDEX(FC_dataCurves_bySN!B2:EJ2=F2,0),)
L2: =SUBSTITUTE(ADDRESS(1,G2+1,4),"1",""). This gives the letter ref for the column, BC in this case. It's offset by "1" because the serial number is in the first column.
The hard part seems to be getting the column number for the tail (the point at which the gas has decayed to within 5% of the starting value). I can't use the same range for the array because the 1st value the index/match function finds is on the increasing side of the curve and I need the value on the declining side. I figured to use the peak as the starting point for my array but can't get the function to accept a variable input for the starting column. Here's what I wrote
{=MATCH(TRUE,INDEX(INDIRECT(CONCATENATE("FC_datacurves_bySN!",L2,A2)):EH2<0.3220,0),)}
The above gives a #value ! Error and when I look at the results of the individual parts the index array indicates volatile. Since my starting value for the array isnt constant I need a way to use a variable within the index/match array.
| AW | AX | AY | AZ | BA | BB | BC | BD | BE | BF | BG | BH | BI | BJ | BK | BL | BM | BN | BO | BP |
| 0.30419 | 0.30364 | 0.30727 | 0.32168 | 0.32888 | 0.3329 | 0.33526 | 0.33724 | 0.33455 | 0.33449 | 0.33361 | 0.33262 | 0.33125 | 0.33015 | 0.32877 | 0.32712 | 0.32558 | 0.32493 | 0.32322 | 0.32262 |
<colgroup><col span="19"><col></colgroup><tbody>
</tbody>
