•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How to set return value no higher than a number (10%), no lower than a number (5%) but ignore zeros
Our take
To create a new column that returns values from your “Total Percent” column while adhering to specific limits, you can apply a formula that captures all your parameters. You want values to be capped at 10%, set a minimum of 5%, and ignore zeros. In cell B2, use the formula: `=IF(A2=0, "", MAX(0.05, MIN(0.10, A2)))`. This formula ensures that any zero remains blank, while values below 5% return 5%, and those above 10% are capped at 10%. Adjust
I have a column of values under “Total Percent” (A2-A10). In a new column (B2-B10), Trying to return the number at least 5% but no more than 10%. Currently the column A has some negative percents, that I want column B to return at least 5%. And some values say 15% that I want to cap (show) at 10%. Where the value currently has 0%, I want to leave it alone and return 0 or just leave it blank.
For max 10%, I can type in column B the formula =MIN(0.10,[@Total Percent]]) and it returns 10% when column has a value of 25%. How do I amend my formula to capture the other two parameters? Thanks!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Tagged with
#AI formula generation techniques#Excel alternatives for data analysis#formula generator#rows.com#natural language processing for spreadsheets#generative AI for data analysis#Excel compatibility#Excel alternatives#Total Percent#5%#10%#percent#return value#formula#column#MIN#cap#values#column B#negative percents