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?

2 Replies
best response confirmed by Kim-Kay (Contributor)



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!