Forum Discussion
Limit Formula Results to Max of 100%
I need to set a limit of 100% on a field that contains the formula =(AK102/500)*100. I have the format of the cell set as custom with the type as 0.00\%. My data can cause the value to exceed 100% but I don't want it to. I need the actual % that the formula generates to show unless it exceeds 100%. In that case, I want it to show 100%. I've tried MIN and MAX functions, IF statements, and I've tried Data Validation, but I just can't seem to get it. Can someone help?
I'm not sure I understand. I suspect you want:
=MIN(1, AK102/500)*100
(Note the 1 is the same as 100%. You could write 100% instead, if you wish.)
But I suspect that should be formatted as Number, not Percentage.
IOW, I suspect AK102/500 returns the percentage of AK102 out of 500. I presume you multiply by 100 so that the percentage is displayed as a decimal number without "%".
I'm not sure I understand. I suspect you want:
=MIN(1, AK102/500)*100
(Note the 1 is the same as 100%. You could write 100% instead, if you wish.)
But I suspect that should be formatted as Number, not Percentage.
IOW, I suspect AK102/500 returns the percentage of AK102 out of 500. I presume you multiply by 100 so that the percentage is displayed as a decimal number without "%".
- YES! That's it exactly! I was using that odd format because I was using =MIN(100, AK102/500)*100 and it wasn't working. I was trying everything!
I changed my cell format back to percentage with the formula you provided and it worked! Woo-hoo! Thank you so much!
