(1) gives the general form of an IF statement in Excel. The spaces surrounding parens are for clarity; I think Excel doesn't care about them. Note the quotes around the second two parts of the IF statement to indicate the character strings that Excel will insert depending on whether the test condition is met or not.
(1) IF ( condition, "condition_met", "else" ) (2) gives an example. You could have a column with this statement to divide the tokens in to "early" tokens (before 1400) and late tokens (1400 and after).
(Here and in the following examples, "A" refers to column for effective date of first attestation, "B" to the column for the number of syllables, and "C" to the column for stress.)
(2) IF ( A2<1400, "early", "late" ) The test condition can be complex. Often you'll impose conditions that involve an AND statement. Suppose you want to investigate the influence of effective date on stress shift. You could identify the tokens attested before 1100 that have retained the French stress with an IF statement like the following. First, the relevant AND statement:
(3) AND ( A2<1100, B2=2, C2=1 ) You could edit the earlier IF statement by pasting the AND statement in (3) over the condition in (2). You could then say "21" if the condition is met (mnemonic for 2-syllable word with stress on 1), and "-" for else.
(4) IF ( AND ( A2<1100, B2=2, C2=1 ), "21", "-" ) The IF statement in (5) would give you tokens with the same date of first attestation that have undergone stress shift. The underlining is for clarity.
(5) IF ( AND ( A2<1100, B2=2, C2=2 ), "22", "-" ) If you use simple IF statements like (4) and (5), you'll have to record the pre-1100 tokens that have undergone stress shift and the ones that haven't in separate columns. You can save space and record both in the same column by collapsing the two IF statements into a single recursive IF statement.
What I mean by an IF statement being recursive is that the "else" part can itself be an IF statement. This sounds simple, and it is simple, and yet nevertheless it is easy to get the parens all screwed up. In order to avoid tearing out your hair, try copying an IF statement into the clipboard and then pasting it over its own "else" statement in the formula bar. This will give you a legal IF statement that you can then edit to say what you want. For instance, you can take the IF statement in (4), repeated here as (6), and turn it into (7).
(6) IF ( AND ( A2<1100, B2=2, C2=1 ), "21", "-" )
(7) IF ( AND ( A2<1100, B2=2, C2=1 ), "21", IF ( AND ( A2<1100, B2=2, C2=1 ), "21", "-" ) ) original . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . copy of original . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . In (8), I've edited the inner IF statement in (7) to give a recursive IF statement that lets you record the stress-shifted and non-stress-shifted tokens in a single column. (Here and in the following examples, make sure to stretch the page so that the IF statement fits on a single line without a line break.)
(8) IF ( AND ( A2<1100, B2=2, C2=1 ), "21", IF ( AND ( A2<1100, B2=2, C2=2 ), "22", "-" ) ) original . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . edited copy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Of course, more complex examples with further embedded IF statements are possible. There is a limit of 7 IF statements. Notice that the number of final parens corresponds to the number of IFs.
Here are two examples. (9) is roughly analogous to (8), but for the three syllable case. (10) is a more general version for all polysyllabic words.
(9) IF ( AND ( A2<1100, B2=3, C2=1 ), "31", IF ( AND ( A2<1100, B2=3, C2=2 ), "32", IF ( AND ( A2<1100, B2=3, C2=3 ), "33", "-" ) ) )
(10) IF ( AND (A2<1100, B2>2, C2=1 ), "poly French", IF ( AND ( A2<1100, B2>2, C2=B2 ), "poly Germanic", IF ( AND (A2<1100, B2>2 ), "whoa", "-" ) ) )
Depending on the chart type that you choose to make your graphs, Excel doesn't always label the X axis the way you want.
- If your chart type is "XY (scatter)" and your dates are not simple numbers (for instance, if they are date ranges like "1500-1519"), the X axis labels will default to "1, 2, 3, 4, ...". You will need to change the date ranges to simple numbers (say, the midpoint of the range, "1510"). You can replace the old cell range, or copy it and revise the copy. Select the chart, and find where to edit the X axis values (under something like "Chart > Source Data > Series"). Enter the proper cell range under something like "X values".
- If your chart type is "line graph", the X axis labels ought to display correctly. If not, select the chart, and find where to edit the X axis values (under something like "Chart > Source Data > Series"). Enter the proper cell range under something like "Category (X) axis values".