EXCEL FORMULAS AND FUNCTION ISSUES

The indirect function returns a reference to the specified string.

sumif is a conditional summation function.

The b$1 in the formula refers to the b1 cell of the worksheet where the formula is located, which holds the table name of a worksheet. & is a string concatenation symbol, so indirect(b$1&"!a:

a"Returns the data region of column A in a worksheet (specified by B1).

Similarly, indirect(b$1&"!e:e"Returns the data region of column E in a worksheet (specified by B1).

The meaning of the whole formula is as follows:

Sum the contents of column E in column A of a worksheet (specified by B1) for rows whose value is equal to A2.

Assuming that the contents of cell b1 are "Table 1", the contents of the whole formula are equivalent to:

sumif (Table 1!).a:a,a2,table 1!e:e) So that you can understand.

Assuming that the data is in A1 An, the mean R1 exists in cell B1 (note that the maximum and minimum values may be repeated):

b1: (sum(a1:a10)-sumif(a1:

a10,max(a1:a10))-sumif(a1:a10,min(a1:

a10)))/sum(if((a1:a10=min(a1:a10))+a1:

a10=max(a1:a10)),0,1))

After entering, use Ctrl+Shift+Enter to enter the formula, and {} (array formula) will appear

Then calculate the mean r2 less than r1:

sum(if(a1:a10 input and then use ctrl+shift+enter to enter the formula, {} (array formula) will appear

The above r2 contains the minimum value, if it does not contain the minimum value, it needs to be modified.

The formula is: =if(iseven(right(trunc(a1,1)))trunc(a1,1),round(a1,1))).

Please click Enter a description.

Rounding, every five odd or even not entered, entered.

round(a1,1),2)=0)

That is, after rounding operations, the last 2 decimal places of the data are encountered. At the end of x5, when x is an even number, just remove this 5, and the rest will follow the rounding rule!?

If this is the case, the formula for keeping 1 decimal place is:

round(a2,1)"5")*mod(left(right(a2,2)),2)=0)

with a custom function.

=max(a1:a100)

min(a1:a100)

All you need to do is enter this function in the Population or Growth column. Negative growth, you can directly filter it.

C2 cell input formula =if(sumproduct((e$2:e$10=a2)*(f$1:h$1=b2)*f$2:h$10)=0,"Not checking in","Check-in")

1.Calculate the length of service: You can use the year function For example, if the start date is in cell A1, then cell B1 = year(now())year(A1).

year(now()) is equal to the number of years today, and the result is 2014

year(a1) is equal to the number of years of employment, for example, if someone joined the company on 1980-1-1, then the result is 1980, and the two are subtracted from 34 years.

If you want to be more precise, for example, people were hired in January of 80, and today is the year.

Then use =yearfrac(a1,now(),3) to get the result that the year is the year which can be rounded up to adulthood or 35 years as needed.

2.The second question is the same as above, you can use the monthr function for example, if the start date is in cell A1, then cell B1 = month(A1) to get the result of 1, which is January.

The above two questions are all input formulas, drag down, and then you can filter.

3.The third question,I don't know what you want to ask.,It's an ordinary formula.,Enter = in the j3 cell and then use the mouse to select the two cells respectively.,The U.S. dollar symbol is generated by pressing the F4 key on the keyboard.。

It's better to take the ** and say it at once.