# Maximum amount 100,000

#### singcbl

##### Well-known Member
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.

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

##### MrExcel MVP
=max(range,10000)

#### singcbl

##### Well-known Member

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

##### MrExcel MVP
sorry - several too many beers this evening already!

=min(range,100000)

#### singcbl

##### Well-known Member

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.

##### MrExcel MVP
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

##### Well-known Member

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

#### Seti

##### Well-known Member
Maybe this?

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

#### Jonmo1

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

edited,
See my next post on page 2
Code:
``=MAX(MAX(A1:A5)*(SUM(A1:A5)<=100000),100000*(SUM(A1:A5)>100000))``

#### lenze

##### Legend
Code:
``=MIN(MAX(A1:A5),100000)``

lenze

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,675
Messages
5,838,716
Members
430,566
Latest member
ChanchalSingh

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back