Index function complications

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 :rofl:

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 isn’t constant I need a way to use a variable within the index/match array.

[TABLE="width: 1283"]
<colgroup><col span="19"><col></colgroup><tbody>[TR]
[TD]AW[/TD]
[TD]AX[/TD]
[TD]AY[/TD]
[TD]AZ[/TD]
[TD]BA[/TD]
[TD]BB[/TD]
[TD]BC[/TD]
[TD]BD[/TD]
[TD]BE[/TD]
[TD]BF[/TD]
[TD]BG[/TD]
[TD]BH[/TD]
[TD]BI[/TD]
[TD]BJ[/TD]
[TD]BK[/TD]
[TD]BL[/TD]
[TD]BM[/TD]
[TD]BN[/TD]
[TD]BO[/TD]
[TD]BP[/TD]
[/TR]
[TR]
[TD="align: right"]0.30419[/TD]
[TD="align: right"]0.30364[/TD]
[TD="align: right"]0.30727[/TD]
[TD="align: right"]0.32168[/TD]
[TD="align: right"]0.32888[/TD]
[TD="align: right"]0.3329[/TD]
[TD="align: right"]0.33526[/TD]
[TD="align: right"]0.33724[/TD]
[TD="align: right"]0.33455[/TD]
[TD="align: right"]0.33449[/TD]
[TD="align: right"]0.33361[/TD]
[TD="align: right"]0.33262[/TD]
[TD="align: right"]0.33125[/TD]
[TD="align: right"]0.33015[/TD]
[TD="align: right"]0.32877[/TD]
[TD="align: right"]0.32712[/TD]
[TD="align: right"]0.32558[/TD]
[TD="align: right"]0.32493[/TD]
[TD="align: right"]0.32322[/TD]
[TD="align: right"]0.32262[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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?

Something like:


Excel 2010
ABCDEFGHIJKLMNOPQRS
10.780.8110.220.690.780.410.370.320.910.0390.350.320.190.40.510.830.0390.95
2
311
Sheet2
Cell Formulas
RangeFormula
A3{=MATCH(TRUE,$A$1:$S$1=0.05*A1,0)}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Something like:

Excel 2010
ABCDEFGHIJKLMNOPQRS

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]0.78[/TD]
[TD="align: right"]0.81[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.22[/TD]
[TD="align: right"]0.69[/TD]
[TD="align: right"]0.78[/TD]
[TD="align: right"]0.41[/TD]
[TD="align: right"]0.37[/TD]
[TD="align: right"]0.32[/TD]
[TD="align: right"]0.91[/TD]
[TD="align: right"]0.039[/TD]
[TD="align: right"]0.35[/TD]
[TD="align: right"]0.32[/TD]
[TD="align: right"]0.19[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]0.51[/TD]
[TD="align: right"]0.83[/TD]
[TD="align: right"]0.039[/TD]
[TD="align: right"]0.95[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A3[/TH]
[TD="align: left"]{=MATCH(TRUE,$A$1:$S$1=0.05*A1,0)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Yes, like that. In my case $A$1 is variable. In row 2 the array may need to start at $C$2, and in row 3 the array may start at $E$3. The array has to start at the peak value of the gas percentage and that happens at a different point each time a part is tested. Each row represents a different part.
 
Upvote 0
If the peak value is calculated elsewhere as you said above then maybe:


Excel 2010
ABCDEFGHIJKLMNOPQRST
10.304190.0153640.307270.321680.328880.33290.335260.337240.334550.334490.0153640.332620.331250.330150.328770.327120.0153640.324930.323220.32262
2
30.30727
411
Sheet2
Cell Formulas
RangeFormula
A4{=MATCH(TRUE,IF(COLUMN($A$1:$T$1)>=MATCH(A3,$A$1:$T$1,0),IF($A$1:$T$1=A3*0.05,TRUE)),0)}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top