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.

 

[PaddyD]

=max(range,10000)

 

[singcbl]

PaddyD,

I must be doing something wrong. The cell value for

A1 = 10
A2 = 120000
A3 = 67
A4 = 340
A5 = 23

When using the formula max=(A1:A5,100000) I got a result of 120,000

 

[PaddyD]

sorry - several too many beers this evening already!

=min(range,100000)

 

[singcbl]

ADVERTISEMENT

PaddyD,

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.

 

[PaddyD]

why no if()?? in my drunken state, I can't think of a native function for what you're after, so any formula we come up with is going to have to evaluate a conditional, whether impicit or otherwise.

 

[singcbl]

ADVERTISEMENT

I am trying to explore other alternatives rather than using the If function. But, if there is no other way so be it.

 

[Seti]

Maybe this?

=MIN(100000,MAX(A1:A5))

 

[Jonmo1]

=IF(SUM(A1:A5)<100000,MAX(A1:A5),100000)

edited,
sorry, didn't read the part about wanting to NOT use IF..
See my next post on page 2

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

 

[lenze]

=MIN(MAX(A1:A5),100000)

lenze