Excel Formula to Calculate the Percentage Above a 28% Target
Question details
Calculate how much each percentage in column B exceeds a 28% target, returning zero for values from 0% through 28%.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Column B contains percentages, and column C should automatically display only the amount above the target.
- Observed behavior
- The desired result is 0% for inputs such as 0%, 25%, and 28%, and 22.3% for an input of 50.3%.
Check that column B contains numeric percentages entered as values such as 25% or 50.3%, not whole numbers such as 25 or 50.3. Decide whether negative inputs should also return zero or need separate handling.
Use MAX to return only the excess above 28%
Use this concise formula when any value at or below 28%, including a negative value, should return zero.
Click cell C2, enter =MAX(0,B2-28%), and press Enter. The formula subtracts the target from B2 and returns zero if the difference is negative.
Select the result cells in column C, then choose Home > Number > Percent Style. Use Increase Decimal if needed to display a result such as 22.3%.
Drag the fill handle at the lower-right corner of C2 down through the remaining rows. Check that 25% returns 0%, 28% returns 0%, and 50.3% returns 22.3%.
Use IF and AND to test the 0%–28% range explicitly
Use the accepted formula when you want to explicitly test whether the input falls between 0% and 28%, inclusive.
Calculate the Amount Above 28% in WPS Spreadsheet
WPS Spreadsheet supports the MAX, IF, and AND formulas used for this calculation. Open your Excel workbook, enter the formula in column C, and format the results as percentages.
- 1. Open your workbook: Launch WPS Spreadsheet and use Open to select the Excel workbook containing the percentages in column B.
- 2. Enter the threshold formula: Select C2, type =MAX(0,B2-28%), and press Enter to return zero or the amount above the target.
- 3. Format and fill the results: Select the output cells and choose Home > Percent Style. Adjust decimal places as needed, then drag C2's fill handle down the column.
- 4. Save the calculated workbook: Choose Menu > Save As and select the Excel .xlsx format to keep the workbook available for use in Microsoft Excel.
Frequently Asked Questions
Why does =28%-B2 give the wrong result for this calculation?
It subtracts the input from the target, so 25% produces 3% and 50.3% produces -22.3%. To return only positive excess above the target, enter =MAX(0,B2-28%) in C2.
What if I want 50.3% to return -22.3% instead?
Use =MIN(0,28%-B2) in C2. This returns 0% for inputs at or below 28% and a negative difference for inputs above 28%. Format column C with Home > Number > Percent Style.
Why does the result show 0.223 instead of 22.3%?
Excel stores percentages as decimal values. Select the result cell and choose Home > Number > Percent Style, then increase the decimal places to show 22.3%.
Can I put the target percentage in a separate cell?
Yes. Enter 28% in E1 and use =MAX(0,B2-$E$1) in C2. The absolute reference $E$1 keeps the target fixed when you fill the formula down.
Will the formula work if column B contains 50.3 rather than 50.3%?
Not as written: 50.3 is a whole-number value, whereas 50.3% is stored as 0.503. If column B consistently uses whole-number percentages, enter =MAX(0,(B2-28)/100) in C2 and format the output as a percentage.
