# Column A + Column B Sum Calculation (Excel Training Exercise)
This process adds the numbers in the first column of a sample Excel workbook to the numbers in the second column and records each total in a third column. It exists as a training/demonstration exercise: the same business outcome is produced three different ways — writing results into the sheet live row by row, building the whole table in memory and writing it in one pass, or inserting Excel `SUM` formulas so the spreadsheet does the arithmetic itself. The operator picks which of the three techniques to run before starting; only one runs per execution.
## At a glance
| | |
|---|---|
| **Trigger** | Manual. The operator edits the "Set Exercise Path Variable" step in `Main.xaml` to choose path 1, 2 or 3, then starts the run. No queue, schedule or event source exists in the source. |
| **Frequency** | Not recorded in the source |
| **Systems used** | Microsoft Excel (desktop application, must be installed on the machine running the bot) |
| **Inputs** | Workbook `Data\Input\RPADev-S04P01-CalculatingSums-Sample-Columns.xlsx`; worksheet **Source** inside it (two unnamed numeric columns, no header row); the chosen path number 1–3 |
| **Outputs** | Path 1 → worksheet **Sheet1** (columns A, B, C filled cell by cell). Path 2 → worksheet **Sheet2** (three-column table written in one pass from cell A1). Path 3 → worksheet **Sheet3** (columns A and B plus live `=SUM(A1,B1)`-style formulas in column C). Plus a console log line confirming which path completed. |
| **Typical run** | Not recorded in the source. The exercise brief embedded in `Main.xaml` lists 11 expected totals, suggesting a small sample of roughly 11 rows. |
| **Owner** | Not recorded in the source |
## Before you start
- **Excel must be installed** on the machine that runs the automation. The developer noted this explicitly against both visible-Excel paths: *"Microsoft Excel must be installed on the machine… Tends to have lower performance than system file operations, especially as the dataset gets larger. Supports .xls and .csv files."*
- **The input workbook must be present** at `Data\Input\RPADev-S04P01-CalculatingSums-Sample-Columns.xlsx`, relative to the automation's own folder.
- **The workbook must contain a worksheet named `Source`** holding the original, untouched two columns of numbers. Every path reads from `Source`; none of them writes back into it.
- **The destination worksheets should already exist.** The developer's notes record that a worksheet named `Source` was created to hold the original data and that `Sheet2` was created by hand for the Path 2 exercise; the source says nothing about where `Sheet1` and `Sheet3` came from. The automation does not create any of them. Confirm the one relevant to your chosen path is present before running.
- **Column values must be numeric.** Developer note: *"columnA and columnB are double data types. String and integer types will throw errors."* Text in either column will fail the run.
- **Close the workbook** before starting, so the bot can take control of it. Paths 1 and 3 will open Excel on screen — do not click in the Excel window while the bot is working.
- No credentials, logins or network access are required.
## Procedure
### Stage 1 — Choose which of the three techniques to run
1. Open `Main.xaml` and find the step named **Set Exercise Path Variable**. It writes a value into the control setting `printMethod` (the switch that decides which technique runs). Developer note: *"Set the PrintMethod value to 1, 2 or 3 to run the appropriate exercise path."*
2. Set the value to:
| Value | Technique |
|---|---|
| 1 | Excel stays open; results written live, row by row into **Sheet1** |
| 2 | Excel stays closed; totals built in memory, written to **Sheet2** in one pass |
| 3 | Excel `SUM` formulas written into **Sheet3** |
As shipped, this step sets the value to **1**.
3. Start the run. The automation reads the value and branches:
- **If the value is 1, 2 or 3, then** it calls the matching sub-process from the `Methods` folder (Stages 2–4 below) and afterwards logs `Operation 01 completed.` / `Operation 02 completed.` / `Operation 03 completed.`
- **If the value is anything else** (including the declared default of `0`), **then** nothing is calculated. The automation logs `No action taken. Set the PrintMethod value to 1, 2 or 3 to run the appropriate exercise path.` and ends. No file is touched.
### Stage 2 — Path 1: open Excel and fill in results live, row by row
Run only if the chosen value is **1**. Sub-process: `Methods\RPADev-S04P01-CalculatingSums-Method01.xaml`. Developer note: *"Keeps the Excel open and writes the results in real time, row by row so you can see the changes."*
1. Open `Data\Input\RPADev-S04P01-CalculatingSums-Sample-Columns.xlsx` in Excel, **visible on screen**, and keep it open for the whole of this stage.
2. Read the whole used range of worksheet **Source** into memory as a table, treating the first row as data (no header row).
3. For each row of that table, in order:
1. Note the row's position in the table (0 for the first row, 1 for the second, and so on).
2. Take the value in the **first** column as *A* and the value in the **second** column as *B*. Developer note: *"Hard coded column values for this exercise"* — the columns are addressed by position, not by name.
3. Calculate the total as a whole number: `Convert.ToInt32(A + B)`. Because A and B are read as decimals, any fractional part is resolved into an integer at this point.
4. On worksheet **Sheet1**, write *A* into cell `A<row>`, *B* into cell `B<row>`, and the total into cell `C<row>`, where `<row>` is the noted position plus 1 (so the first table row lands on spreadsheet row 1).
4. Because writes happen one cell at a time with Excel on screen, you can watch the sheet fill in. Verify column C against the expected totals in Stage 5.
### Stage 3 — Path 2: build the totals in memory, then write once
Run only if the chosen value is **2**. Sub-process: `Methods\RPADev-S04P01-CalculatingSums-Method02.xaml`. Developer note: *"Keeps the Excel closed, set the column values in the memory DataTable and adds all the table to a new Excel files at once, in the end… I created another worksheet in the source file named 'Sheet2' for this exercise. The data is read from the 'Source' worksheet."*
1. Read worksheet **Source** of `Data\Input\RPADev-S04P01-CalculatingSums-Sample-Columns.xlsx` straight from the file, without opening Excel on screen. No header row. Developer note: *"Get the data from the file."*
2. Add a third column to the in-memory table to hold the totals. Developer note: *"TypeArgument of DataColumn has errors when trying to put the value back in, thus setting this type to Object"* — the column is deliberately left untyped so the total can be stored without a conversion error.
3. For each row of the table:
1. Take the first column as *A* and the second as *B*.
2. Calculate `Convert.ToInt32(A + B)`.
3. Store the total in the newly added third column of that same row. Nothing is written to Excel yet.
4. When every row has a total, write the completed three-column table into worksheet **Sheet2** in a single operation, starting at cell **A1**, with no header row.
*Ambiguity worth flagging:* this final write step names only the bare file name `RPADev-S04P01-CalculatingSums-Sample-Columns.xlsx`, without the `Data\Input\` folder, whereas the read step uses the full path. It is therefore unclear from the source whether the totals land back in the input workbook or in a second copy created in the automation's working folder. Check both locations after the first run and confirm which file was updated.
### Stage 4 — Path 3: let Excel do the arithmetic with SUM formulas
Run only if the chosen value is **3**. Sub-process: `Methods\RPADev-S04P01-CalculatingSums-Method03.xaml`. Developer note: *"Calculates the sum by using Excel formulas in the original file."*
1. Open `Data\Input\RPADev-S04P01-CalculatingSums-Sample-Columns.xlsx` in Excel, **visible on screen**, and keep it open for the whole of this stage.
2. Read worksheet **Source** into memory as a table, no header row.
3. Add a third, untyped column to hold the formulas (same reason as in Stage 3).
4. Record how many rows the table has. Developer note: *"CellCount will be used in the write range."*
5. For each row, keeping a counter that starts at 0 and is increased by 1 at the start of each row — developer note: *"CellNumber is initialized to 0 and incremented in this activity because cells are not zero indexed"*:
1. Build the formula text `=SUM(A<n>,B<n>)` where `<n>` is the current counter value. Developer note: *"Target formula example: SUM(A1,B1)."*
2. Store that text in the third column of the current row. No arithmetic is performed by the automation on this path.
6. Write the table into worksheet **Sheet3** over the range `A1:C<row count>`, with no header row. Excel then evaluates the formulas in column C itself, so the totals shown are live and recalculate if A or B is edited.
### Stage 5 — Confirm the result and close out
1. Check the console log for the confirmation line matching the path you ran: `Operation 01 completed.`, `Operation 02 completed.` or `Operation 03 completed.` Absence of this line means the run did not reach the end.
2. Open the destination worksheet (`Sheet1`, `Sheet2` or `Sheet3`) and check column C against the expected totals recorded in the exercise brief inside `Main.xaml`:
| Row | Expected total |
|---|---|
| 1 | 195 |
| 2 | 3394 |
| 3 | 18068 |
| 4 | 679 |
| 5 | 335 |
| 6 | 95585 |
| 7 | 7 |
| 8 | 7537 |
| 9 | 7775 |
| 10 | 965 |
| 11 | 3668 |
3. After paths 1 and 3, Excel has been on screen throughout. The source does not show an explicit save-and-close step, so check the workbook state and save/close it manually if it is still open.
## Exceptions and recovery
| Condition | What the automation does | What a human should do |
|---|---|---|
| Chosen path value is not 1, 2 or 3 (e.g. the declared default `0`) | Takes the fallback branch, logs `No action taken. Set the PrintMethod value to 1, 2 or 3 to run the appropriate exercise path.`, ends. No workbook is touched. | Reopen `Main.xaml`, set the value in **Set Exercise Path Variable** to 1, 2 or 3, and rerun. |
| Excel not installed, or Excel cannot open the workbook | Error is caught by the single catch-all handler wrapped around the whole of `Main.xaml`; one log line is written and the run ends. No retry, no notification, no cleanup. | Confirm Excel is installed and licensed on the run machine, that no other process holds the file open, and rerun. |
| Input workbook missing from `Data\Input\`, or worksheet `Source` missing | Same catch-all handler: log line, run ends. | Restore the workbook to `Data\Input\RPADev-S04P01-CalculatingSums-Sample-Columns.xlsx` and confirm the `Source` worksheet exists with the two number columns. |
| Non-numeric value in column A or B. Developer warning: *"String and integer types will throw errors."* | Same catch-all handler: log line, run ends. Rows already written by path 1 remain in `Sheet1`; paths 2 and 3 write nothing at all, since their single write happens after all rows are processed. | Clean the offending cell in `Source` so both columns contain only decimal numbers, then rerun. For path 1, clear any partially written rows in `Sheet1` first to avoid stale results. |
| Destination worksheet (`Sheet1`/`Sheet2`/`Sheet3`) does not exist | Not handled explicitly. The write will either fail into the catch-all handler or, depending on Excel behaviour, create the sheet — the source does not settle this. | Create the sheet by hand before rerunning, as the original developer did. |
| Any other failure inside the chosen sub-process | Caught by the catch-all handler, which writes a log line. The outline shows this log step **with no text configured**, so the recorded failure detail may be blank or unhelpful. | Do not rely on the log for diagnosis. Rerun with the automation in a monitored/debug mode to see the actual error, and treat a run with no `Operation NN completed.` line as failed. |
## Data handled
| Item | What it holds | Where it comes from |
|---|---|---|
| `printMethod` | The chosen technique: 1, 2 or 3. Declared with default `0`, but overwritten to `1` by the **Set Exercise Path Variable** step. | Set by the operator inside `Main.xaml` before the run |
| `dataTable` (all three paths) | The in-memory copy of the source numbers — one row per spreadsheet row, first column = A values, second column = B values, read with no header row. In paths 2 and 3 a third column is added to it. | Worksheet **Source** of `Data\Input\RPADev-S04P01-CalculatingSums-Sample-Columns.xlsx` |
| `columnA`, `columnB` | The two numbers being added for the row currently being processed. Held as decimals — the developer notes text or whole-number types will throw errors. | Read from column positions 0 and 1 of the current table row |
| `sum` (paths 1 and 2) | The whole-number total for the current row, `Convert.ToInt32(columnA + columnB)`. In path 1 it is written straight to cell `C<row>`; in path 2 it is stored in the third table column. | Calculated |
| `columnC` (paths 2 and 3) | The extra, deliberately untyped third column added to the in-memory table to carry either the calculated total (path 2) or the formula text (path 3). | Created by the automation |
| `formula` (path 3) | The Excel formula text for the current row, e.g. `=SUM(A1,B1)`. | Built from the 1-based row counter |
| `cellCount` (path 3) | The number of source rows, used to size the write range `A1:C<cellCount>` on **Sheet3**. | Row count of the in-memory table |
## Not determinable from the source
- Schedule, process owner, and who decides which of the three paths should be run — nothing in the source indicates automated triggering.
- Row volume in normal operation. Only the 11 expected totals quoted in the exercise brief hint at size; the loops simply process whatever the `Source` sheet contains.
- Whether the Path 2 output lands back in `Data\Input\RPADev-S04P01-CalculatingSums-Sample-Columns.xlsx` or in a same-named file in the process working folder — the write step gives only the bare file name.
- Whether `Sheet1`, `Sheet2` and `Sheet3` must exist beforehand. The developer's notes confirm only that the `Source` and `Sheet2` worksheets were created by hand; the source is silent on where `Sheet1` and `Sheet3` came from, and the automation does not create them.
- What text the catch-all error handler actually writes, and where those logs are read.
- Any downstream consumer of the calculated sums — none is referenced anywhere in the source.
- Whether the workbook is saved and closed explicitly after Paths 1 and 3, which leave Excel open on screen.
How this SOP was checked
Generated from the project's source files and audited against them. Audit verdict: minor issues, confidence high.
Covers all four workflows in correct order: the manual printMethod selection and switch (cases 1/2/3 plus default log), the try/catch wrapper with its untexted log line, and each of the three method workflows including source paths, sheet names (Source/Sheet1/Sheet2/Sheet3), the Convert.ToInt32(columnA+columnB) calculation, the rowIndex+1 cell addressing, the added untyped column C, cellNumber increment and =SUM(A n,B n) formula text, cellCount sizing, and the bare-filename ambiguity in Method02's write. Correctly flags what the source cannot support (schedule, owner, volume, log destination, save/close). Remaining problems are small over-readings of developer notes and activity names.
1 correction from the audit was applied to the procedure above.
Remaining minor notes, not corrected:
- Stage 4 step 1 and Stage 5 step 3 ('Excel has been on screen throughout') — Asserts Path 3 opens Excel visibly. The outline shows no visibility setting; only Method01's activity name ('Excel Application Scope Visual Manual') and the exercise brief support on-screen behaviour. Method03's scope is named only 'Excel Application Scope Original File'. (Say Path 3 works in an Excel application session against the original file; note that whether the window is visible is not specified in the source.)
- Stage 4 step 6 / Data handled (cellCount) — Describes the Sheet3 write as covering 'the range A1:C<row count>'. In the source that string is supplied as the write's starting-cell parameter (StartingCell="A1:C"+cellCount), not a range parameter. (State that the value 'A1:C<row count>' is passed as the starting cell, which is unusual and its exact effect cannot be determined from the source.)
- Stage 5 step 2 expected-totals table — Assigns the 11 sums from the Main.xaml brief to rows 1–11. The brief lists the values under 'Sums' without row numbers. (Present the 11 values as the expected set in listed order, noting the row mapping is inferred.)
- Before you start ('Column values must be numeric… Text in either column will fail'); Exceptions table, non-numeric row — The developer note ('columnA and columnB are double data types. String and integer types will throw errors') concerns the automation's internal variable types, not the content of the spreadsheet cells; the SOP presents cell-content failure as established fact. (Quote the note as a data-typing constraint inside the automation and mark the consequence for non-numeric cell content as inference.)