1
00:00:00,080 --> 00:00:04,466
This fictional order report shows fifty-five dollars in its SUM cell,

2
00:00:04,466 --> 00:00:07,391
although four order amounts add to three hundred.

3
00:00:07,391 --> 00:00:12,143
Three amounts look numeric but are stored as text, so SUM skips them.

4
00:00:12,143 --> 00:00:16,163
We will identify those cells, convert only the amount column with

5
00:00:16,163 --> 00:00:20,184
a helper formula, and verify the repaired total without changing order

6
00:00:20,184 --> 00:00:22,012
IDs that begin with zero.

7
00:00:22,612 --> 00:00:25,113
Open a copy of the practice workbook.

8
00:00:25,113 --> 00:00:30,115
The Order ID column contains codes such as zero-zero-seven-one, and the

9
00:00:30,115 --> 00:00:35,117
Raw amount column contains one hundred twenty-five, eighty, forty, and fifty-five dollars.

10
00:00:35,117 --> 00:00:39,405
The first three amounts are text; the last is a real number.

11
00:00:39,405 --> 00:00:44,407
The raw SUM is fifty-five because it sees only that last numeric cell.

12
00:00:45,007 --> 00:00:49,655
Select the raw amount cells and inspect their values and any Excel warning.

13
00:00:49,655 --> 00:00:53,589
A green error indicator can identify a number stored as text,

14
00:00:53,589 --> 00:00:56,450
but its presence depends on error-checking settings.

15
00:00:56,450 --> 00:01:00,383
The reliable clue in our exercise is that SUM of B

16
00:01:00,383 --> 00:01:04,674
two through B five is fifty-five, even though the visible amounts

17
00:01:04,674 --> 00:01:06,104
add to three hundred.

18
00:01:06,104 --> 00:01:08,250
Do not diagnose by alignment alone.

19
00:01:08,850 --> 00:01:12,929
In Excel for the web, enter equals VALUE open parenthesis B

20
00:01:12,929 --> 00:01:15,154
two close parenthesis in C two.

21
00:01:15,154 --> 00:01:17,008
Fill it through C four.

22
00:01:17,008 --> 00:01:21,086
Microsoft documents VALUE as the web route for converting numeric text.

23
00:01:21,086 --> 00:01:25,907
In C five, link the already numeric B five with equals B five.

24
00:01:25,907 --> 00:01:31,098
The helper values should be one hundred twenty-five, eighty, forty, and fifty-five.

25
00:01:31,698 --> 00:01:34,872
Add the four converted values in the helper column.

26
00:01:34,872 --> 00:01:39,809
One hundred twenty-five plus eighty plus forty plus fifty-five equals three hundred.

27
00:01:39,809 --> 00:01:43,689
The worked answer file is not needed to trust the result:

28
00:01:43,689 --> 00:01:47,568
compare every row and check that SUM of C two through

29
00:01:47,568 --> 00:01:49,331
C five is three hundred.

30
00:01:49,331 --> 00:01:54,269
If one VALUE call errors, inspect that particular source string before converting more cells.

31
00:01:54,869 --> 00:01:59,362
The order IDs start with zeros because they are identifiers, not quantities.

32
00:01:59,362 --> 00:02:01,234
Leave column A as text.

33
00:02:01,234 --> 00:02:06,101
Converting zero-zero-seven-one to a number can remove its leading zeros

34
00:02:06,101 --> 00:02:09,471
and change how you match or export the code.

35
00:02:09,471 --> 00:02:13,590
A fix for amount arithmetic must stay inside the amount column.

36
00:02:13,590 --> 00:02:16,960
Check all four IDs again before copying results elsewhere.

37
00:02:17,560 --> 00:02:21,336
The downloadable workbook preserves the problem, and the answer check

38
00:02:21,336 --> 00:02:23,979
lists both totals and every converted amount.

39
00:02:23,979 --> 00:02:27,000
It also explains why the IDs remain text.

40
00:02:27,000 --> 00:02:31,154
An Excel error indicator may offer Convert to Number on desktop,

41
00:02:31,154 --> 00:02:34,174
but check the selected column before using it.

42
00:02:34,174 --> 00:02:38,706
This lesson solves a skipped-text SUM, not every reason a spreadsheet

43
00:02:38,706 --> 00:02:40,216
total can be wrong.