Excel Formula to Calculate Percentage Change Between the Last Two Nonblank Values
Question details
Calculate the percentage increase or decrease between the two most recent nonblank values in an Excel row.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user tracks weekly or periodic values across a row and wants a formula that ignores blank cells and compares only the latest two entries.
- Observed behavior
- The result should return the percentage change from the second-to-last nonblank value to the last nonblank value.
Ensure the values in the range are numeric and that at least two nonblank values are available. Use a version of Excel that supports the LET, FILTER, and TAKE functions.
Use LET, FILTER, and TAKE to compare the last two nonblank values
Use this dynamic-array formula when your data runs horizontally across a row and blank cells may appear between recorded values.
The formula filters out blank cells, keeps the final two values, and divides the newest value by the previous value before subtracting 1. Format the result cell as Percentage to display a readable increase or decrease.
Click the cell where you want the week-over-week percentage change to appear, such as AA4.
Type or paste =LET(last2,TAKE(FILTER(B4:Z4,B4:Z4<>""),,-2),INDEX(last2,1,2)/INDEX(last2,1,1)-1), replacing B4:Z4 with your actual data range.
On the Home tab, select the Percentage Style (%) button in the Number group, then adjust decimal places if needed.
Drag the fill handle down from the formula cell, or copy and paste it into matching rows. Excel will adjust the row references automatically.

Calculate the Latest Nonblank Percentage Change in WPS Spreadsheets
WPS Spreadsheets lets you organize periodic values, calculate percentage changes, and format results as percentages in a familiar spreadsheet workspace. Enter the formula in a result cell, then fill it down to compare the latest two recorded values for each row.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the Excel workbook containing your weekly values.
- 2. Choose an output cell: Click an empty cell beside the row of values where the percentage change should be displayed.
- 3. Enter the percentage change formula: Enter =LET(last2,TAKE(FILTER(B4:Z4,B4:Z4<>""),,-2),INDEX(last2,1,2)/INDEX(last2,1,1)-1), updating B4:Z4 for your data row.
- 4. Apply percentage formatting: Select the formula cell, then use Home > Number Format > Percentage and set the preferred decimal places.
- 5. Fill the formula to additional rows: Drag the cell's fill handle down to calculate the latest percentage change for each corresponding row.

Frequently Asked Questions
What does the percentage change formula return when the latest value is lower?
It returns a negative percentage. For example, if the previous nonblank value is 100 and the latest value is 80, the result is -20% after percentage formatting.
How do I change the formula for values stored in a column instead of a row?
For a vertical range such as B4:B100, use =LET(last2,TAKE(FILTER(B4:B100,B4:B100<>""),-2),INDEX(last2,2)/INDEX(last2,1)-1). Then format the result cell using Home > Percentage Style.
Why does the formula show a #CALC! or #VALUE! error?
Check that the selected range contains at least two nonblank numeric values. Errors can also occur if the Excel version does not support LET, FILTER, or TAKE.
Can I show zero instead of an error when fewer than two values exist?
Yes. Wrap the formula with IFERROR, such as =IFERROR(LET(last2,TAKE(FILTER(B4:Z4,B4:Z4<>""),,-2),INDEX(last2,1,2)/INDEX(last2,1,1)-1),0).
Does the formula ignore blank cells between weekly values?
Yes. FILTER(B4:Z4,B4:Z4<>"") removes all blank cells before TAKE selects the two most recent remaining values.




