Max/Min Range Cell Value in Excel

[MrBill]

Hello- Thx in advance for the help.

Is there an elegant way to create a maximum calculation range in excel. I'll try to setup an example:

- Lets say Cell A5 calculates out to 30 (say the formula is =SUM(A1:A4)
- But I'd like to have A5 value to be a minimum of 15 or a maximum of 25
- Thus, in this example, A5 should be 25 (since A5's sum value is greater than 25)
- But if A5's Sum value was 10, then it would show a value of 15
- In otherwords, the values need to have a min and max (given range)

I tried to play with nested if/then and Min/Max, but wasn't able to get it to do what I wanted.

Any ideas? Hope I explained it ok.

Thx!

[NBVC]

Try:

=MEDIAN(15,25,SUM(A1:A4))

[ChemistB]

Try
=IF(SUM(A1:A4)<15,15, MIN(SUM(A1:A4),25))
Does that work for you?

Edit: I like NBVC's better

[mewingkitty]

So if it's under 15, you want it to show 15, if it's over 25, you want it to show 25, and if it's anything in the middle, it shows the exact value, eg 16, 17, 21, 23, 24 etc.
?

Edit:
I think that's the first time I've ever seen the median function.
Shnazzy.

[MrBill]

Thanks Everyone. The Median function is neato... never seen that before. Works like a charm.

Thanks!