Cap a value

[Alenz]

I have an IF that is working fine but I need to add that if the value is greater than 117, it should just state 117.

This is the part that works:
=IF(A9<>"",BO9-BR9-BS9-BQ9,"")

Now I need to add that if the answer to the above is greater than 117, it should give 117. But if it is less than 117, it should give the calculated value (BO9-BR9-BS9-BQ9).

Help please??

 

[AliGW]

Try this:

=IF(A9<>"",IF((BO9-BR9-BS9-BQ9)>117,117,BO9-BR9-BS9-BQ9),"")

 

[ChrisGT7]

An alternative:

=IF(A9<>"",MIN(117,BO9-BR9-BS9-BQ9),"")

 

[Alenz]

An alternative:

=IF(A9<>"",MIN(117,BO9-BR9-BS9-BQ9),"")

Hi ChrisGT7

Thanks for this! Seems to work fine except I need it to also just state 0 or preferably noting if A9 does not contain a value.
Currently it gives the correct answer when A9 has a value but then also gives 117 when A9 does not have any value.

Can you help?

 

[ChrisGT7]

Is A9 totally empty? If it contains a space, then it is not empty and it should return 117 according to MIN().

To avoid checking for spaces in A9, try this one: =IF(LEN(TRIM(A9))>0,MIN(117,BO9-BR9-BS9-BQ9),"")

 

[Alenz]

Is A9 totally empty? If it contains a space, then it is not empty and it should return 117 according to MIN().

To avoid checking for spaces in A9, try this one: =IF(LEN(TRIM(A9))>0,MIN(117,BO9-BR9-BS9-BQ9),"")

Yay! My hero!

There wasn't a space in A9 but there was a formula though. Thanks for your help!