Can 9E307 still be used like this in Excel?

This article brings you relevant knowledge about excel, which mainly introduces the usage of 9E307 in Excel. 9E307, the complete expression is "9E 307", in Excel it It belongs to scientific notation, which means "9*10^307". Let's take a look at it. I hope it will be helpful to everyone.

Related learning recommendations: excel tutorial

Today I will talk about a number: 9E307, the complete expression is 9E 307. In Excel, it belongs to scientific notation, which means 9*10^307, which is a value very close to the maximum value that Excel can accommodate.

Then what is the use of this thing?

Snap your fingers and let me give you a few small examples.

1. Ignore the sum of error values

As shown in the figure below, there are error values ​​in the B2:B9 area, and now you need to sum in cell B10.

Some friends will use the SUM function directly:

=SUM(B2:B9)

Result An error value will be returned. This is because the SUM function will not ignore the error value. Once it exists within the summing range, the error value itself will be returned - the legendary misunderstanding of Yang Guo at first sight.

A friend said that this is simple. You can change the SUM function to the following:

=SUM(B2,B4:B6 ,B8:B9)

Little fist punches your chest, you are really awesome~

……

The correct answer formula is to use the SUMIF function ：

=SUMIF(B2:B9,”

SUMIF omits the third parameter summing area, so the first parameter is used by default The condition area serves as the summation area, and the condition for summation is less than the value 9E307, that is, all values ​​are summed.

2. The final query

As shown in the figure below, if you need to query the last value appearing in column A.

The best formula for this problem is to use the LOOKUP function.

The last value in column A

=LOOKUP(9E 307,A:A)

This It is a fixed routine of LOOKUP. When the search value is greater than all values ​​of the same type within the search range, the last value of the same type is returned.

9E307 is a very large value, so the above formula will always return the last value in column A.

Extending this routine can solve two common problems.

As shown in the figure below, A1:M8 is the data details. You need to query the month of each person's last assessment in column N.

The so-called month of final evaluation is the month in which the last numerical value appears.

For example, the last evaluation month for "Seeing Starlight" in cell A2 is March (I quit my job to go to the Olympics in March), and the last evaluation month for "Fat Secretary" in cell A4 is December (he will be evaluated in August). The assessment scores for December have been prepared) Enter the following formula in cell

and copy it downwards:

=LOOKUP(9E 307,B2:M2,B$1 :M$1)

B2:M2 is the single-row query range, B$1:M$1 is the corresponding single-row result range, 9E307 is larger than all similar data in the query range, so the last occurrence is returned The month corresponding to the value.

As shown in the figure below, column A is the data source, and you need to query the consecutive values ​​that appeared before

The reference formula for column B is as follows:

=LOOKUP(9E 307,–LEFT(A2,ROW($1:$15)))

LEFT(A2,ROW($1:$15)) part, extract 1 from the left side of cell A2 in sequence , 2, 3, 4... up to 15 digits of data, the return result is: 2, 20, 204, 204 sees, 204 sees...

Then through the minus operation (–), the calculation result of the LEFT function Convert to numeric value. At this time, plain text cannot perform mathematical operations, such as –204, it will return the error value #VALUE!.

The calculation result of this part is a memory array

{2;20;204;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!; #VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

LOOKUP naturally ignores error values, and then uses a value larger than the query range If 9E307, which has a large value, is queried, the last value 204 will be returned.

That’s it.

Related learning recommendations: excel tutorial

The above is the detailed content of Can 9E307 still be used like this in Excel?. For more information, please follow other related articles on the PHP Chinese website!