Formulas & functions
How a function works, with worked examples you can paste.
- Cash Flow Diagram Excel Formula and SetupExcel has no single built-in cash flow diagram formula. If periods are in A2:A6 and signed cash flows are in B2:B6, enter…
- Capitalize in Excel: Formulas, Flash Fill, and TipsTo capitalize each word in Excel, enter =PROPER(A2) in a spare cell, replacing A2 with the cell that contains your text, then…
- Randomize Cells in Excel: Formulas, Steps, and TipsYes. In Excel for Microsoft 365 on Windows, you can randomize a list with a formula such as =SORTBY(A2:A10,RANDARRAY(ROWS(A2:A10))), or add random…
- Rank Values in Excel: Formulas, Ties, and Sorting ExplainedYes. In Excel for Microsoft 365 on Windows, use RANK.EQ to assign a rank to a value, or RANK.AVG to give tied…
- Link Cells in Excel: Formulas, Sheets, and WorkbooksYes. In Excel, you can link cells with a formula that displays another cell’s value, whether the source is on the same…
- Can You Use Formulas in an Excel Table?Yes. Excel tables support formulas, and a formula entered in a table column can become a calculated column that fills down automatically;…
- How to Rank in Excel: Formulas, Ties, and Sorting DataYes, Excel can rank numbers with the RANK.EQ function. If the scores are in B2:B5, enter =RANK.EQ(B2,$B$2:$B$5,0) in C2 and copy it…
- Weighted Average in Excel: Formulas and ExamplesYes. In Excel, calculate a weighted average by multiplying each value by its weight, adding those products, and dividing by the sum…
- Can Excel Table Headers Use Formulas? What to DoNo. In Excel for Microsoft 365 on Windows, a table header is a column label, not a worksheet formula cell, so Excel…
- Calendar Year Excel Formula: Extract the Year from a DateIf a date is in cell A2, use =YEAR(A2) to return its calendar year as a number. For example, if A2 contains…
- Calendar Math in Excel: Dates, Workdays, and FormulasCalendar math in Excel means using real date values and date functions to add or subtract days, find month boundaries, build calendars,…
- Weighted Average in Excel: Formula and ExamplesTo calculate a weighted average in Excel, multiply each value by its weight, add those products, and divide by the sum of…
- P Value in Excel: Formulas for Common Statistical TestsTo calculate a p value in Excel, choose a test that matches your data, then use its worksheet function or calculate the…
- Calendar Date Formula in Excel: ExamplesThe most useful Excel calendar date formula is =DATE(year,month,day), which combines a year, month, and day into a valid date. For example,…
- Calculating ROI in Excel: Formulas, Examples, and TipsTo calculate return on investment (ROI) in Excel, subtract the investment cost from the value it generated, then divide by the cost.…
- Average in Excel: Formulas, Examples, and Common FixesTo calculate an average in Excel, use =AVERAGE(B2:B10) if the numbers are in cells B2 through B10. The formula adds the numeric…
- PV in Excel: Calculate Present Value With the PV FunctionTo calculate present value in Excel, use the PV function: =PV(rate,nper,pmt,[fv],[type]). For example, with a 5% annual rate and three end-of-year payments…
- Calculating NPV and IRR in Excel: Formulas and StepsTo calculate NPV in Excel, discount the future cash flows with NPV and add the initial cash flow separately; to calculate IRR,…
- Calculating NPV in Excel: Formula, Steps, and ExamplesTo calculate net present value in Excel, place the discount rate in B1, the initial investment in B2, and the later cash…
- Calculating PMT in Excel: Formula, Payments, and ExamplesTo calculate a fixed periodic loan payment in Excel, use the PMT function, making sure the interest rate and number of periods…
- Calculating Loan Payments in Excel: PMT and ExamplesTo calculate a fixed monthly loan payment in Excel, enter the loan amount, annual interest rate, and term in years, then use…
- Calculating Moving Average in Excel: Formulas and TipsTo calculate a moving average in Excel, average a fixed-size group of consecutive values, then copy the formula down one row at…
- Calculating Markup in Excel: Formulas and ExamplesTo calculate markup in Excel, subtract cost from selling price, then divide the difference by cost: with cost in B2 and selling…
- Calculating IRR in Excel: Formula, Examples, and TipsTo calculate IRR in Excel, put each periodic cash flow in one cell, including the initial investment as a negative number, then…
- Calculating CAGR in Excel: Formula, Examples, and TipsTo calculate CAGR in Excel, divide the ending value by the beginning value, raise the result to the power of one divided…
- Calculate a Weighted Average in Excel: Formula and ExamplesTo calculate a weighted average in Excel, multiply each value by its weight, add those products, and divide by the sum of…
- Weighted Average Rate in Excel: Formula and ExamplesTo calculate a weighted average rate in Excel, multiply each rate by its corresponding weight, add those products, and divide by the…
- BMI Formula in Excel: Calculate It Step by StepTo calculate BMI in Excel, divide weight in kilograms by height in metres squared; for example, with weight in B2 and height…
- Calculate Weekday from Date in Excel: Formulas and TipsTo calculate a weekday from a date in Excel, use =WEEKDAY(A2,2) if the date is in A2; Monday returns 1 and Sunday…
- Weighted Average Life in Excel: Formula and ExampleTo calculate weighted average life (WAL) in Excel, multiply each principal repayment by the time it is paid, add those products, and…