Tip:
Highlight text to annotate it
X
Let's create a chart that compares the difference of intensity of the stop
consonants /p/, /t/, /k/, /b/, /d/, /g/ and of /s/.
So here I have a file called Diagrama de intensidad, where I
have prepared a template with a bar chart. There are no bars yet
because I don't have any values in this table.
I also have the intensity list file that was created by the script.
Now, I'd like to copy the data to the first file that I
opened, so that they can use it in my chart. To do this,
I will right-click on intensit_list.xls
on the bottom of the window. Move or Copy... I will copy it to the first file
that I opened, which Diagrama de intensidad
and I will check this check box to create a copy.
And now I can close the other Excel file.
So for each sound file, I now have the minimum and maximum intensity for
each interval that I labeled.
Now, I'd like to calculate the difference in intensity between the consonants and
the following vowel. I will create a new column
and I'll call Int difference. And for every consonant,
I will subtract its minimum intensity from the maximum intensity of the
following vowel.
So for this first consonant, which is /b/, I will type =
then I will take the max intensity of the following vowel
minus than minimum intensity of /b/.
And press Enter. And I will do the same for the next consonant.
= max intensity over the following vowel
minus the minimum intensity of the consonant. Enter.
Now, I don't have to enter this formula
for every consonant. I can simply select these two cells,
and be sure to select both, and then I place my mouse pointer
over this blue square and drag it all the way to the bottom of my file
to fill in the formula into every other cell. So now
I have the intensity difference for every consonant. I'll go back to my bar chart
by clicking on Diagrama de intensidad on the bottom.
And now I need to fill in the average differences
in intensity for each of these constants.
Now, I could go back and manually calculate the averages,
but that would take a really long time. Instead, I can make Excel do it for me
by entering a formula, much like the one I used before, but this time
It will be a formula for an average based on some condition.
So let's start by calculating the average difference
in intensity for the constant /p/.
=AVERAGEIF, which is conditional average
open parentheses... And, as you can see, I need to give it three
arguments separated by commas: range, criteria and average range.
The first argument, range,
is the range of the cells that I need to check for the criteria.
And my criteria will be the consonant, in this case /p/,
so the range of cells is the interval name. And I'll go back to intensity_list.xls
and I'll scroll all the way to the top
and I will click on B to select the column with Interval names.
So that's the range of values that I'll ask Excel to check for a criteria before
taking an average.
Then I will type in comma, and the next argument
is the criteria, which is the consonant, in this case /p/,
so let's go back to Diagrama de intensidad, but instead of
entering the value 'p', I will enter the number of the cell that contains the
constant /p/,
which is B4 comma. And the last argument is
average range, which the range of cells which contain the values
that I want to take the average of. I'd like to take the
average of a difference in intensity, which was the column
that I created not so long ago, and so to select, it I'll go back to intensity list
and click on the H to select the whole column.
I'll close the parentheses and press Enter.
So now I can simply fill in the rest of the cells, much like I did before
with the intensity difference. So I click on the cell,
put the most pointer over the blue square in the bottom right corner
and drag it down.
And there you have it, a bar chart showing the
intensity difference for stop consonants /p/, /t/, /k/, /b/, /d/, /g/ and also for /s/.