[Introduction] [Signal arithmetic] [Signals and noise] [Smoothing] [Differentiation] [Peak Sharpening] [Harmonic analysis] [Fourier convolution] [Fourier deconvolution] [Fourier filter] [Wavelets] [Peak area measurement] [Linear Least Squares] [Multicomponent Spectroscopy] [Iterative Curve Fitting] [Hyperlinear quantitative absorption spectrophotometry] [Appendix and Case Studies] [Peak Finding and Measurement] [iPeak] [iSignal] [Peak Fitters] [iFilter] [iPower] [List of downloadable software] [Interactive tools]

These examples were done in what is called the "A1" reference style, where the columns are referred to by letters; it's also possible to use the "R1C1" reference style, where both the and the rows columns are referred to by numbers. For example =INDIRECT("R"&A2&"C"&A1,FALSE)", with the row number in A2 and the columns number in A1. (The "FALSE" just means that the "R1C1" reference style is used). The "R1C1" reference style allows

You can use the same technique to compute

For functions that require

Columns J and K of this sheet also show how to use the "IF" and "AND" functions to copy data from columns A and B into columns J and K only those data points that fall between the two specific x limits.

If desired, you can add more data to the end of columns A and B, limited only be the range of the match functions in cells F2 and F3 (which are initially set to 1000, but that could be as large as you need). The total number of numerical values in the data set is computed in cell I15, using the "COUNT" function (which, as the name suggests, counts the number of cells in a range that contains numbers and does not count empty cells or cells with letters).

(1) Define the range of values, either directly or using the indirect function, e.g. INDIRECT("B"&F2&":B"&F3)

(2) Determine the maximum y value in that range with the

MAX function, e.g.: MAX(INDIRECT("B"&F2&":B"&F3))

(3) Determine the row number in which that number appears

with the MATCH function, e.g.: MATCH(H20,B7:B1000,0)+ROW(A6)

(4) Determine the value of x in that row with the INDIRECT function,

e.g.: INDIRECT("A"&H21)

Each step references the results of the one before it. These steps are illustrated in the same "SpecialFunctions.xlsx" spreadsheet in column H, rows 20-23. The result is that the maximum y (21.5) occurs at x=28. These steps can even be combined into one long formula (cell H23), although this is harder to read, and harder to document, than the formulas for the separate steps. The peak finder spreadsheet uses this technique.

This page is part of "