Xu Hướng 6/2023 # Arctan Excel Functions: Use Atan And Atan2 To Calculate Inverse Tangent # Top 14 View

Bạn đang xem bài viết Arctan Excel Functions: Use Atan And Atan2 To Calculate Inverse Tangent được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.

There are two functions for calculating the inverse tangent or arctan in Excel. These two functions are ATAN and ATAN2 (“atan” is short for arctangent), and they each have specific uses depending on the desired results that you’d like to obtain and the inputs available. In general, I’d recommend using ATAN if:

You are only concerned with the first quadrant of the unit circle

You don’t know the x- and y-values

However, use the ATAN2 function if:

You want to return angles in all four quadrants of the unit circle

You know the x- and y-values

Using ATAN to Calculate Arctan in Excel

The ATAN function returns a result between -π/2 and π/2 radians (or -90 and 90 degrees), or in other words, in the first and fourth quadrants.

The syntax is:

ATAN(number)

There is only one argument to ATAN: the number from which you want to calculate the inverse tangent. And since there is only one argument, Excel cannot determine which quadrant the solution should be in.

The absolute values of the angles never exceed 90 degrees, and

Values in the second (II) quadrant in column C are now in the fourth quadrant in column F.

Values in the third (III) quadrant in column C are now in the first quadrant in column F.

Plotting the results on the unit circle looks like this:

Since there is only one argument to the ATAN function, Excel calculates the value of y/x before if evaluates the ATAN function.

It cannot determine, then, whether the input to ATAN is negative because the x-value or the y-value is negative. Therefore, it can’t tell if the result should be in the second quadrant or the fourth quadrant, so it just defaults to the fourth quadrant.

Likewise, when the input value is positive, the original quadrant could be either the first (both x and y positive) or third (both x and y negative).

We could use a complex nested IF function to get the results back into all four quadrants, but fortunately Excel has another function called ATAN2 to help us out.

Using ATAN2 to Calculate Arctan in Excel

The ATAN2 function can also be used to calculate the inverse tangent in Excel. This function returns a result between -π and π radians (or -180 and 180 degrees), using all four quadrants. The syntax is:

ATAN2(x_num,y_num)

There are two arguments:

By inputting two values to the function, Excel can determine which quadrant the value belongs in.

Quadrant 1 if both x and y are positive

Quadrant 2 if x is negative and y is positive

Quadrant 3 if both x and y are negative

Quadrant 4 if x is positive and y is negative

So if we use the formula

=DEGREES(ATAN2(x_num,y_num))

We get the following results:

Hopefully, now you understand the limitations of ATAN and when it’s better to use ATAN2 to calculate the inverse tangent or arctan in Excel.

In general, use ATAN if:

You are working only in the first quadrant

You don’t know the x- or y-values

Use the ATAN2 function if:

You need the angles in all four quadrants

The x- and y-values are known

How To Use The Excel Sumif Function

SUMIF is in a group of eight functions in Excel that split logical criteria into two parts (range + criteria). As a result, the syntax used to construct criteria is different, and SUMIF requires a cell range for the range argument, you can’t use an array.

SUMIF only supports a single condition. If you need to apply multiple criteria, use the SUMIFS function. If you need to manipulate values that appear in the range argument (i.e. extract the year from dates to use in criteria) see the SUMPRODUCT and/or FILTER functions.

Videos

Basic usage

In the worksheet shown, there are three SUMIF examples. In the first example (G6), SUMIF is configured to sum values greater than 100. In the second example (G7), SUMIF returns the sum of values where the color is “red”. In the last example (G8), SUMIF is configured to sum values where the state is “CA” (California).

SUMIF

(

B6:B10

"Jim"

D6:D10

)

// Rep = Jim

SUMIF

(

C6:C10

"ca"

D6:D10

)

// State = CA

Notice the equals sign (=) is not required when constructing “is equal to” criteria. Also notice SUMIF is not case-sensitive. You can sum values where the Rep is Jim using “jim” or “Jim”.

Criteria in another cell Not equal to

To express "not equal to" criteria, use the "" operator surrounded by double quotes (""):

Again notice SUMIF is not case-sensitive.

Blank cells

SUMIF can calculate sums based on cells that are blank or not blank. In the example below, SUMIF is used to sum the amounts in column C depending on whether column D contains "x" or is empty:

SUMIF

(

D5:D9

C5:C9

)

// blank

The best way to use SUMIF with dates is to refer to a valid date in another cell, or use the DATE function. The example below shows both methods:

SUMIF

(

B5:B9

"<"

DATE

(

2019

C5:C9

)

Wildcards

The SUMIF function supports wildcards, as seen in the example below:

SUMIF

(

B5:B9

"mi*"

C5:C9

)

// begins with "mi"

SUMIF

(

B5:B9

"*ota"

C5:C9

)

// ends with "ota"

SUMIF

(

B5:B9

"????"

C5:C9

)

// contains 4 characters

See below for more SUMIF formula examples.

Notes

SUMIF only supports one condition. Use the SUMIFS function for multiple criteria.

When sum_range is omitted, the cells in range will be summed.

Cell references in criteria are not enclosed in quotes, i.e. "

The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters (zero or more).

To find a literal question mark or asterisk, use a tilde (~) in front question mark or asterisk (i.e. ~?, ~*).

SUMIFS requires a range, you can't substitute an array.

How To Use The Excel Countif Function

COUNTIF is in a group of eight functions in Excel that split logical criteria into two parts (range + criteria). As a result, the syntax used to construct criteria is different, and COUNTIF requires a cell range, you can’t use an array.

COUNTIF only supports a single condition. If you need to apply multiple criteria, use the COUNTIFS function. If you need to manipulate values in the range argument as part of a logical test, see the SUMPRODUCT and/or FILTER functions.

Basic example

In the worksheet shown above, the following formulas are used in cells G5, G6, and G7:

COUNTIF

(

B5:B12

"jim"

)

// count name = "jim"

COUNTIF

(

C5:C12

"ca"

)

// count state = "ca"

Notice COUNTIF is not case-sensitive, “CA” and “ca” are treated the same.

Double quotes (“”) in criteria

In general, text values need to be enclosed in double quotes (“”), and numbers do not. However, when a logical operator is included with a number, the number and operator must be enclosed in quotes, as seen in the second example below:

COUNTIF

(

A1:A10

100

)

// count cells equal to 100

COUNTIF

(

A1:A10

"jim"

)

// count cells equal to "jim"

Value from another cell

A value from another cell can be included in criteria using concatenation. In the example below, COUNTIF will return the count of values in A1:A10 that are less than the value in cell B1. Notice the less than operator (which is text) is enclosed in quotes.

COUNTIF

(

A1:A10

"<"

)

// count cells less than B1

Not equal to

To construct “not equal to” criteria, use the “” operator surrounded by double quotes (“”). For example, the formula below will count cells not equal to “red” in the range A1:A10:

Blank cells

COUNTIF can count cells that are blank or not blank. The formulas below count blank and not blank cells in the range A1:A10:

COUNTIF

(

A1:A10

)

// blank

Dates

The easiest way to use COUNTIF with dates is to refer to a valid date in another cell with a cell reference. For example, to count cells in A1:A10 that contain a date greater than the date in B1, you can use a formula like this:

The safest way hardcode a date into COUNTIF is to use the DATE function. This ensures Excel will understand the date. To count cells in A1:A10 that contain a date less than April 1, 2023, you can use a formula like this

COUNTIF

(

A1:A10

"<"

DATE

(

2020

))

// dates less than 1-Apr-2020

Wildcards

The wildcard characters question mark (?), asterisk(*), or tilde (~) can be used in criteria. A question mark (?) matches any one character and an asterisk (*) matches zero or more characters of any kind. For example, to count cells in a A1:A5 that contain the text "apple" anywhere, you can use a formula like this:

COUNTIF

(

A1:A5

"*apple*"

)

// cells that contain "apple"

To count cells in A1:A5 that contain any 3 text characters, you can use:

COUNTIF

(

A1:A5

"???"

)

// cells that contain any 3 characters

The tilde (~) is an escape character to match literal wildcards. For example, to count a literal question mark (?), asterisk(*), or tilde (~), add a tilde in front of the wildcard (i.e. ~?, ~*, ~~).

Notes

COUNTIF is not case-sensitive. Use the EXACT function for case-sensitive counts.

COUNTIF only supports one condition. Use the COUNTIFS function for multiple criteria.

Cell references in criteria are not enclosed in quotes, i.e. "

The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters (zero or more).

To match a literal question mark or asterisk, use a tilde (~) in front question mark or asterisk (i.e. ~?, ~*).

COUNTIF requires a range, you can't substitute an array.

COUNTIF returns incorrect results when used to match strings longer than 255 characters.

COUNTIF will return a #VALUE error when referencing another workbook that is closed.

How To Use The Excel Sort Function

The SORT function sorts the contents of a range or array in ascending or descending order with a formula. The result from SORT is a dynamic array of values that will “spill” onto the worksheet into a range. If values in the source data change, the result from SORT will update automatically.

The SORT function takes four arguments: array, sort_index, sort_order, and by_col. The first argument, array, is the range or array to be sorted. This is the only required argument. By default, the SORT function will sort values in ascending order using the first column in array. Use the optional arguments sort_index and sort_order to control which column to sort by, and the order to sort by (ascending or descending). Sort_index should be a number corresponding to the column (or row) used for sorting. For example, to sort by the third column in a range of data, use 3 for sort_index. The optional sort_order argument determines sort direction. Use 1 for ascending order and -1 for descending order. By default, the SORT function will sort data vertically by rows. To sort a range horizontally by columns, set the fourth argument, by_col, to TRUE.

Basic Examples

To sort a range by the first column in ascending order:

SORT

(

range

)

// sort by column 1, ascending

SORT

(

range

)

// sort by column 1, ascending

To sort a range by the first column in descending order:

SORT

(

range

)

// sort by column 1, descending

To sort a range by the second column in descending order:

SORT

(

range

)

// sort by column 1, ascending

To sort a range horizontally (by column) using values in row 1 in descending order, set the fourth argument, by_col, to TRUE or 1:

SORT

(

range

TRUE

)

SORT

(

range

)

Example – sort by score

In the example shown above, data includes names in column B and scores in column C. In cell E5, the SORT function is used to sort the data by score in descending order:

SORT

(

B5:C14

)

// sort by scores in descending order

The SORT function extracts all values, sorted in descending order by score, and results “spill” into the range E5:F14. To sort by score in ascending order, omit sort_order or to 1 like this:

SORT

(

B5:C14

)

// sort by score in ascending order

SORT

(

B5:C14

)

// sort by score in ascending order

Sort by more than one level

Unlike the SORTBY function, the SORT function does not provide a way to sort data by more than one level. However, by using array constants for sort_index and sort_order, it can be done. This video explains how.

SORT is a new function available in Excel 365 only.

Cập nhật thông tin chi tiết về Arctan Excel Functions: Use Atan And Atan2 To Calculate Inverse Tangent trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!