百分比值格式化需求：按列数值位数调整格式及特殊值处理
Alright, let's walk through how to implement this conditional percentage formatting requirement properly. The key here is basing the spacing on the entire column's value range, so first we need to check the column's values before applying the format:
First, assess all values in the target column to determine the spacing rule:
When all values have single-digit integer parts (e.g., 4.10%, -9.50%, 0.00% — their whole number portion is 1 digit or less)
- For non-zero values: Format as
+ X.XX%or- X.XX%(one space between the sign and the percentage number) - For 0.00%: Output exactly
0.00%(no sign, no leading space)
- For non-zero values: Format as
When at least one value has a two-digit integer part (e.g., 12.30%, -25.75% — any value with a whole number portion of 2 digits)
- For non-zero values: Format as
+ X.XX%or- X.XX%(two spaces between the sign and the percentage number) - For 0.00%: Output exactly
0.00%(no sign, no leading spaces)
- For non-zero values: Format as
Scenario 1: Column with all single-digit values
Original values: 3.25%, -7.80%, 0.00%
Formatted output (using code block for clarity):
+ 3.25% - 7.80% 0.00%
Scenario 2: Column containing two-digit values
Original values: 6.40%, -18.95%, 0.00%
Formatted output:
+ 6.40% - 18.95% 0.00%
内容的提问来源于stack exchange，提问作者Nemgathos
