Maximum amount 100,000

singcbl

Well-known Member
Joined
Feb 8, 2006
Messages
504
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.
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

singcbl

Well-known Member
Joined
Feb 8, 2006
Messages
504
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
sorry - several too many beers this evening already!

=min(range,100000)
 

singcbl

Well-known Member
Joined
Feb 8, 2006
Messages
504

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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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
Joined
Feb 8, 2006
Messages
504

ADVERTISEMENT

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

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
=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
Code:
=MAX(MAX(A1:A5)*(SUM(A1:A5)<=100000),100000*(SUM(A1:A5)>100000))
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,093
Members
414,501
Latest member
mdhaumyu

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top