Home > Topics > excel > body text

excel financial functions

angryTom
Release: 2019-07-25 16:40:34
Original
23414 people have browsed it

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%')

excel financial functions

##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'})

excel financial functions

##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 .

excel financial functions

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

excel financial functions

##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)

excel financial functions##6. Amount case formula

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

excel financial functions7. 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.

excel financial functions8. Single condition search formula

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

excel financial functions9、 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

excel financial functions10. Multi-condition search formula

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

excel financial functions11. Single condition summation formula

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

excel financial functions12. Multi-condition summation formula

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

excel financial functions13. 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)

excel financial functions

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.

excel financial functions

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.

excel financial functions

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

excel financial functions

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)

excel financial functions

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)

excel financial functions

##20, Chinese ranking formula

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

excel financial functions

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)}

excel financial functions

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

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!