excel financial functions

If you want to know more about excel functions, you can click: Excel Tutorial

This is a comprehensive collection of Excel formulas commonly used in financial accounting. There are 21 in total. I hope it will be useful to friends who work in financial accounting.

1. Text and percentage connection formula

If connected directly, the percentage will be displayed in numbers and needs to be formatted with the Text function before connecting.

='Profit completion rate this month'&TEXT(C2/B2,'0%')

##2. Account Aging analysis formula

Use the lookup function to divide the aging interval

=LOOKUP(D2,G$2:H$6)

If you do not use the auxiliary area , you can use the constant array

=LOOKUP(D2,{0,'less than 30 days';31,'1~3 months';91,'3~6 months';181,'6- 1 year';361,'greater than 1 year'})

##3. Masking error value formulaDisplay the error value generated by the formula as empty

Formula: C2=IFERROR(A2/B2,'')

Description: If it is an error value, it will be displayed as empty, otherwise it will be displayed normally .

4. Completion rate formula As shown in the figure below, the requirements are based on the actual situation of B and C Column the budget number and calculate the completion rate.

Announcement: E2=IF(C3

##5, year-on-year growth rate formula

As shown in the figure below, column B is the accumulation for this year, and column C is the accumulation for the same period last year. It is required to calculate the year-on-year growth rate.

Announcement: E2=(B2-C2)/ IF(C2>0,C2,-C2)

##6. Amount case formula

=TEXT(LEFT(RMB(A2),LEN(RMB(A2))-3),'[>0][dbnum2]G/general format element;[

7. Multi-condition judgment formula

Formula: C2=IF(AND(A2Instructions: Use AND if two conditions are true at the same time, use the OR function if either condition is true.

8. Single condition search formula

Formula 1: C11=VLOOKUP(B11,B3:F7,4,FALSE)

9、 Bidirectional search formula

Formula: =INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0 ))

Instructions: Use the MATCH function to find the location and the INDEX function to get the value

10. Multi-condition search formula

Formula: C35=Lookup(1,0/((B25:B30=C33)*(C25:C30=C34)),D25:D30)

11. Single condition summation formula

Formula: F2=SUMIF(A:A,E2,C:C)

12. Multi-condition summation formula

=Sumifs(c2:c7,a2:a7,a11 ,b2:b7,b11)

13. Alternate column summation formula

Formula H3:=SUMIF($A$2:$G$2,H$2,A3:G3)

If there is no title, you can only use a slightly more complicated formula.

=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)

14. Summing multiple tables at the same position

Formula: b2=SUM(Sheet1:Sheet19!B2)

Description: After deleting or adding a table in the middle of the table, the formula Results are updated automatically.

15. Find two identical formulas

Formula: B2=COUNTIF(Sheet15!A: A,A2)

Note: If the return value is greater than 0, it means it exists in another table, 0 means it does not exist.

16. Multi-condition verification of data from two tables

As shown in the figure below, it is required to check the two tables The quantity difference of the same product and the same model is displayed in column D.

Formula: D10=SUMPRODUCT(($A$2:$A$6=A10)*($B$2:$B$6=B10)*$C$2:$C$6)-C10

17. Personal tax calculation

If A2 is taxable salary, the formula for calculating personal tax is: = 5*MAX(A2*{0.6,2,4,5,6,7,9}%-{21,91,251,376,761,1346,3016},)

18. Accounting accounts Interception formula

First-level subject:=LEFT(B3,FIND('-',B3)-2)

Second-level subject:=MID(B3,FIND ('-',B3) 2,FIND('-',B3&'-',FIND('-',B3) 1)-FIND('-',B3)-3)

Level 3 Subject: =MID(B3,FIND('-',B3&'-',FIND('-',B3) 1) 2,100)

19. Summarize formulas at the same position in multiple tables

If the formats of multiple worksheets are exactly the same, you can use the multi-table sum function of the sum function.

As shown in the figure below, it is required to set a total formula in the summary table to summarize the sum of column B of the first 19 worksheets.

B2 =SUM(Sheet1:Sheet19!B2)

##20, Chinese ranking formula

Formula:C12=SUMPRODUCT(($D$4:$D$9>=D4)*(1/COUNTIF(D$4:D$9,D$4:D$9)))

21. First-in-first-out formula

Reminder: This formula is a very difficult formula and is not recommended for novices and those who are struggling to use it. To understand, it is only for reference by intermediate and advanced users of Excel.

Definition name:

Lj=SUBTOTAL(9,OFFSET(!$E$2,,,ROW()-1))-SUBTOTAL(9,OFFSET(!$B$2,,, ,ROW(!$B$1:$B9)))

G3 formula:

{=SUMPRODUCT(IF(lj>$B$3:B3,$B$3:B3,lj) ,IF(lj>0,$C$3:C3))-SUM($G$2:G2)}

The above is the detailed content of excel financial functions. For more information, please follow other related articles on the PHP Chinese website!