Context:
I am writing a script for a factory that tests audio quality. I have software that returns the transfer function values at 2K different frequencies. I’ve setup a VBA script in Excel to run the software and populate an Excel spreadsheet with the data. However, I then use Octave/MatLab to analyze the data and find the low and high frequencies where the transfer function “rolls off”. Rather than calling Octave/MatLab to do this calculation, I’d like to learn how to do this in VBA/Excel; which will eliminate the need for the factory to install another software.
Question:
How do I make an Excel VBA macro look at a 2×2000 table and find the low frequency and the high frequency where the transfer function is above the reference value at 1 [KHz] minus 3 [dB].
Considering the function I made in Octave/MatLab, how do I do the following in Excel/VBA:
- Find the index/value in the fist column closest to 1000 (actual value likely to be 999.98, but not guaranteed) (call it freqRef)
- Store the adjacent value of the cell in the next column (call it tfRef)
- Review all rows above freqRef for values in the second column that are below “thRef – 3” (i.e. find “Max Freq” for logical conditions “Freq < 1000 & TF < tfRef – 3”)
- Review all rows below freqRef for values in the second column that are below “thRef – 3” (i.e. find “Min Freq” for logical conditions “Freq > 1000 & TF < tfRef – 3”)
History: Here’s the code I wrote in Octave/MatLab. It takes advantage of logical index filters (logic array) so that FREQLOG is all frequencies that satisfy the condition TF(f) - TF@1[KHz] + 3 < 0 (all frequencies where the transfer function is below the reference threshold). I’ve been trying to write this in VBA, but I don’t know how to do it where I keep the indices abstracted.
[~, IDX] = min( abs( FREQ - 1000 ) );
FREQLOG = FREQ(TF - TF(IDX) + 3 < 0);
FREQLO = max(FREQLOG(FREQLOG < 1000))
FREQHI = min(FREQLOG(FREQLOG > 1000))
2
Well if you were to translate this step by step into an Excel array formula you would end up with something like this:
=LET(freq,A2:A11,
Tf,B2:B11,
idx,XMATCH(MIN(ABS(freq-1000)),ABS(freq-1000)),
freqlog,FILTER(freq,Tf<INDEX(Tf,idx)-3),
MAX(FILTER(freqlog,freqlog<INDEX(freq,idx))))
and
=LET(freq,A2:A11,
Tf,B2:B11,
idx,XMATCH(MIN(ABS(freq-1000)),ABS(freq-1000)),
freqlog,FILTER(freq,Tf<INDEX(Tf,idx)-3),
MIN(FILTER(freqlog,freqlog>INDEX(freq,idx))))
but it does remind me of the advice given by a yokel to a gentleman who had got lost in the countryside:
If I were you, sir, I wouldn't start from here
Actually I wouldn’t do it that differently if starting from scratch – maybe use Xlookup to get F(1000) and TF(1000) rather than using IDX plus Index to get the values, then do both filters together.
=LET(freq,A2:A11,
Tf,B2:B11,
d_1000,ABS(freq-1000),
f_1000,XLOOKUP(MIN(d_1000),d_1000,freq),
Tf_1000,XLOOKUP(MIN(d_1000),d_1000,Tf),
MAX(FILTER(freq,(freq<f_1000)*(Tf<Tf_1000-3))))