Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

# Limit Formula Results to Max of 100%

Copper Contributor

# 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 (Copper Contributor)
Solution

# Re: Limit Formula Results to Max of 100%

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 "%".

# Re: Limit Formula Results to Max of 100%

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!
1 best response

Accepted Solutions
best response confirmed by Kim-Kay (Copper Contributor)
Solution

# Re: Limit Formula Results to Max of 100%

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 "%".