Forum Discussion
Formula help: Formula must show any number <50 as 50 and if it is >100 as 100
Hi!
I need a bit of help with this formula that I am trying to setup for a class test result sheet:
I have tried this formula but it keeps saying #VALUE?
=IF(SUM(E8)<50,50,SUM(E8))&=IF(SUM(E8)>100,100,SUM(E8))
This is what the formula must do for me for the candidate final mark:
1. If the total is below 50%, the mark but show 50 (e.g. if the candidate gets 47% it will automatically show it as 50%)
2. If the total is above 100%, the mark but show 100 (e.g. if the candidate gets 122% it will automatically show it as 100%).
3. Any other number between 51 and 99 must show as calculated
I already have the first formula as =IF(SUM(E8)<50,50,SUM(E8)) , which now ensures that the mark below 50 shows 50, but how do I add a 2nd formula for the same number if it might be over 100?
This should work for you:
=MIN(100,MAX(E8,50))
Just so you can see the IF statement working though this is what it would look like:
=IF(E8<50,50,IF(E8>100,100,E8))
It works something like this.. if E8 is smaller then 50, the answer is 50, else if E8 is larger than 100, the answer is 100, else the answer is E8.
Hope that helps.
Thanks
This should work for you:
=MIN(100,MAX(E8,50))
Just so you can see the IF statement working though this is what it would look like:
=IF(E8<50,50,IF(E8>100,100,E8))
It works something like this.. if E8 is smaller then 50, the answer is 50, else if E8 is larger than 100, the answer is 100, else the answer is E8.
Hope that helps.
MIN(MAX()) can be shortened to MEDIAN().
=MEDIAN(50;100;E8)
Thanks so much Detlef Lewin!
This has made my life so much easier! You are awesome!
You are so awesome! Thank you so much! I really appreciate your time helping me out.
Thank you so much!
- :) no problem. Glad it helped.
