Maximum amount 100,000

[singcbl]

I have a range of values lets say in cell A1 to A5. In A7 is where I want to formula to the maximum value but if it exceed 100000 then only show 100000. I am trying to find out if it possible not to use the If Function to achieve the same result.

 

[Jonmo1]

=MAX(MAX(A1:A5)*(SUM(A1:A5)<=100000),100000*(SUM(A1:A5)>100000))

 

[Seti]

Jon,

Why summing up the range? I don't see the OP looking at that.

 

[Jonmo1]

I agree, you had too many beers already.
What I really want is, if the range has value below 100,000, show the highest value in this range, but if the range exceeds 100,000 then only show 100,000 but the catch is not using the If function in the formula.

I'm kinda reading between the lines, The bolded part to me, sounds like if the SUM of the range is below 100,000 then...

 

[Seti]

Ah,

I read that as the max in the range. Guess the OP can choose based on intent.