Excel table formula failure how to do

solutions to the failure of Excel table formulas

Solution to Excel Table formula failure 1: Take a basic example to illustrate that the first three columns in the table add up to calculate the fourth column totals by formula. The current data is correct.

Solution to Excel Table Formula failure 2: Modified AB data, the results of the total remains unchanged. This happens often in relation to some of your settings.

Solution to Excel Table Formula failure 3: Continue to observe, double-click the cell, see the formula is still there, and the formula is correct.

Solution to Excel Table formula failure 4: After just the double click formula, found that can calculate the latest correct results.

Continue to test again and again found that after modifying the data, you must double-click the formula cell to calculate.

Excel table Formula failure Solution 5: The above phenomenon, is generally due to the settings described next. Take excel2003 as an example. Options in the Point Tools menu.

Workaround for Excel table formula failure 6: In the Recalculation tab. Check your computer settings. The default should be the automatic reset setting.

If you change it to a manual calculation, the failure of this example will occur. So why do you have this setup? Isn't this a problem? This is a long story, for the moment to understand, there is the existence of the truth.

Sometimes the Excel file data formula is more, if all is the automatic computation, the computation affects the work repeatedly. Then you need to set up a reasonable manual calculation, and so on to determine the modification of the formula data or something, after the change back.

Excel Table Formula Encyclopedia

1, find duplicate content formula: =if (COUNTIF (A:A,A2) >1, "Repeat", "").

2, the age of birth to calculate the formula: =trunc (DAYS360 (H6, "2009/8/30", FALSE)/360,0).

3, from the input of the 18-digit ID number of the birth calculation formula: =concatenate (Mid (e2,7,4), "/", Mid (e2,11,2), "/", Mid (e2,13,2)).

4, from the input of the ID card number within the system to automatically extract the gender, you can enter the following formula:

=if (LEN (C2) =15,if (MOD) (MID (c2,15,1), 2 = 1, "Male", "female"), IF (MOD (MID (c2,17,1), 2) = 1, "Male", "female"), "C2" in the formula represents the cell that entered the ID number.

1, Sum: =sum (K2:K56)--to the K2 to K56 this area to sum;

2. Average: =average (k2:k56)-an average of K2 K56 area;

3, ranking: =rank (k2,k$2:k$56)--55 students ranked the results;

4. Grade: =if (k2>=85, "excellent", if (k2>=74, "Liang", if (k2>=60, "pass", "fail"))

5, Semester Rating: =k2*0.3+m2*0.3+n2*0.4--assume that K-columns, m columns and n columns are stored in the student's "normal rating", "Midterm", "final" three of the results;

6, the highest score: =max (k2:k56)-K2 to K56 region (55 students) the highest score;

7, the lowest point: =min (k2:k56)-K2 to K56 region (55 students) of the lowest score;

8, the number of fractional segment statistics:

(1) =countif (k2:k56, "100")--the number of 100 points K2 to the K56 region; Suppose the result is stored in a K57 cell;

(2) =countif (k2:k56, ">=95")-k57--the number of K2 K56 to 95~99.5 area; Suppose the result is stored in K58 cell;

(3) =countif (k2:k56, ">=90")-sum (k57:k58)-The number of K2 K56 to 90~94.5 area; Suppose the result is stored in K59 cell;

(4) =countif (k2:k56, ">=85")-sum (k57:k59)-The number of K2 K56 to 85~89.5 area; Suppose the result is stored in K60 cell;

(5) =countif (k2:k56, ">=70")-sum (K57:K60)-The number of K2 K56 to 70~84.5 area; Suppose the result is stored in K61 cell;

(6) =countif (k2:k56, ">=60")-sum (k57:k61)-The number of K2 K56 to 60~69.5 area; Suppose the result is stored in K62 cell;

(7) =countif (k2:k56, "<60")--to find the number of K2 to the K56 region below 60; Suppose the result is stored in K63 cell;

Note: The COUNTIF function can also calculate the number of males and females in a given region.

such as: =countif (c2:c351, "male")--C2 to C351 region (a total of 350 people) the number of men;

9, Excellent rate: =sum (k57:k60)/55*100

10. Passing Rate: =sum (k57:k62)/55*100

11, Standard deviation: =stdev (k2:k56)-K2 to K56 region (55 people) of the performance fluctuations (the smaller the number, indicating that the students of the class differences in performance, on the contrary, that the class exists polarization);

12, the condition sum: =sumif (b2:b56, "male", k2:k56)--Suppose B column holds student's sex, k column holds student's score, then this function returns the result to indicate that the class boy's achievement's sum;

13. Multiple conditions sum: {=sum (if (c3:c322= "male", if (g3:g322=1,1,0))}--assumes C column (c3:c322 area) holds student's sex, g column (g3:g322 area) holds student's class code (1, 2, 3, 4, 5), The result returned by this function indicates the number of boys in the class; This is an array function that presses the Ctrl+shift+enter key combination (produces "{" "}") when it is finished. "{}" cannot be entered manually and can only be produced with a key combination.

14, automatically calculated according to the date of birth: =trunc (DAYS360 (D3,now ())/360,0)

——— assume that the D column holds the student's birth date, and the e-column enters the function to produce the same age as the child.

15. Three Tips in Word:

① three consecutive "~" can get a wavy line.

② enter three consecutive "-" to get a straight line.

Enter three consecutive "=" to get a double straight line.

In Excel, when a cell meets a specific condition, how to display a specific color in another cell such as:

When a1〉1, C1 shows Red

0

When a1<0, C1 shows yellow

The method is as follows:

1, cell C1 cell, point "format" > "conditional Format", condition 1 set to:

Formula =a1=1

2, point "format"-> "Font"-> "Color", click the red Point "OK".

The condition 2 is set to:

Formula =and (a1>0,a1<1)

3, point "format"-> "Font"-> "Color", click Green after the point "OK."

The condition 3 is set to:

Formula =a1<0

Point "format"-> "Font"-> "Color", click the yellow Point "OK".

4, three conditions set, point "OK" that out.

How to control the length of each column in Excel and avoid duplicate entry

1. Define data length with data validation.

Use the mouse to select the range of data you want to enter, point "data"-> "validity"-> "settings", "validity conditions" set to "Allow" "Text Length" equals "5" (Specific conditions can be changed according to your needs).

You can also define some hint information, error warning information and whether to open the Chinese input method, and so on, define the point "OK".

2, with conditional format to avoid duplication.

Select A column, point format-> conditional formatting, set the condition to Formula =countif ($A: $A, $A 1) >1, dot format-> font-> color, and select red after two times "OK."

When you set this up, you can enter data if the length is not correct will be prompted, if the data repeat font will be turned red.

How to identify the difference between column B and column A in Excel?

(i), if it is required to compare the same row of data between A and B columns:

Suppose the first behavior header, click A2 Cell, point "format"-> "conditional Formatting", and set the condition to:

"Cell Value" "is not equal to" =b2

Point "format"-> "Font"-> "Color", select red, point two times "OK".

Use the Format Painter to copy the conditional formatting of the A2 cell down.

Column B can refer to this method setting.

(b) If column A is compared with column B as a whole (that is, the same data is not on the same line):

Suppose the first behavior header, click A2 Cell, point "format"-> "conditional Formatting", and set the condition to:

"Formula" =countif ($B: $B, $A 2) =0

Point "format"-> "Font"-> "Color", select red, point two times "OK".

Use the Format Painter to copy the conditional formatting of the A2 cell down.

Column B can refer to this method setting.

After the above method is set, the data in the AB column is not shaded, and column A has no or B columns with column A and the data marked as red.

How to deal with the order of rows in Excel in batches

Assuming that you have a large number of data (values), you need to sort each row from big to small, how do you operate?

Because you sort by row and by column, you can only have one primary key, and the primary key will be the same when the key is sorted by the time keyword. Therefore, this problem cannot be solved by sorting. The workaround is as follows:

1. Assuming your data is in columns A through E, enter the formula in the F1 cell:

=large ($A 1: $E 1,column (A1))

Use the fill handle to copy the formula to the right and down to the appropriate range.

Your original data will appear in columns F through J in rows from large to small. If necessary, you can copy the paste/value to other places. Note: The 1th step of the formula can be based on your actual situation (data range) for the corresponding changes. If you want to sort from small to large, the formula changes to: =small ($A 1: $E 1,column (A1))

Using function combination to count statistics of multiple conditions

Example: the first act header, column A is "name", column B is "class", c column is "Language results", D is "admission results", now to statistics "class" for "two", "Language score" is greater than 104, "admission results" for "heavy" number. The results are stored in the other columns of this worksheet.

The formula is as follows:

=sum (IF (b2:b9999= "II") * (c2:c9999>=104) * (d2:d9999= "heavy"), 1,0)

Press Ctrl+shift+enter when you finish entering the formula, and let it automatically add the array formula symbol "{}".

How do I determine if a cell contains the specified text?

Assuming that the A1 cell is judged to have "specified text", any of the following formulas can be:

=if (COUNTIF (A1, "*" & "Specify Text" & "*") = 1, "Yes", "none")

=if (ISERROR ("Specify text", a1,1), "None", "there")

To find the number of data that is not duplicated in a region

For example, to find the number of a1:a100 in the range of data, a number of repeated occurrences of only one. There are two methods of calculation: one is to use the array formula:

=sum (1/countif (a1:a100,a1:a100))

Press Ctrl+shift+enter when you finish entering the formula, and let it automatically add the array formula symbol "{}".

The second is to use the product summation function:

=sumproduct (1/countif (a1:a100,a1:a100))

There are a lot of worksheets in the 七、一个 workbook how to quickly sort out a catalog worksheet

1, use the macro 3.0 to remove the name of each sheet, methods:

CTRL+F3 The Custom Name dialog box appears, named X, in the Reference Location box, enter:

=mid (get. Workbook (1), Find ("]", get. Workbook (1)) +1,100)

Are you sure

2, use the HYPERLINK function to insert the connection in bulk, method:

Enter a formula for the A2 cell in the table of contents (typically the first sheet):

=hyperlink ("#") &index (X,row ()) & "'! A1 ", INDEX (X,row ()))

Fills the formula down until an error occurs, and the directory is generated.