I want to limit the result of a calculation to a maximum number...

[Guest]

I have a formula in a cell; =IF(B4=0,0,IF(B6=0,0,B6/(PI()*(B4/12))))

I never want to display a result greater than 600 and if the result is
greater than 600, I want 600 to be displayed.

How do I alter my formula to achieve this?

Darrell

 

[David Biddulph]

=IF(B4=0,0,IF(B6=0,0,MIN(600,B6/(PI()*(B4/12)))))

 

[Niek Otten]

Hi Darrel,

=MIN(IF(B4=0,0,IF(B6=0,0,B6/(PI()*(B4/12)))),600)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have a formula in a cell; =IF(B4=0,0,IF(B6=0,0,B6/(PI()*(B4/12))))
|
| I never want to display a result greater than 600 and if the result is
| greater than 600, I want 600 to be displayed.
|
| How do I alter my formula to achieve this?
|
| Darrell

 

[MartinW]

Hi Darrell,

This should do.
=IF(B4=0,0,IF(B6=0,0,IF(B6/(PI()*(B4/12)>600,600,B6/(PI()*(B4/12))))

HTH
Martin

 

[Guest]

TY, This worked nicely.

I have another Cell that I have an entry which needs to be between 1 and 12
(Whole Numbers) I would like to do the same thing. If someone erroniously
enters a number larger than 12, I would like 12 to be displayed.

I was hoping that the reply to the first question would help me, but since
I'm not starting with a formula, I'm not sure how to enter the logic.

Can you demonstrate how to limit the display to no greater than 12?

Darrell

 

[David Biddulph]

If you want to contrain a value to be between 1 and 12, you can either use
=IF(A1<1,1,IF(A1>12,12,A1)) or
=MAX(1,MIN(A1,12)) or
=MEDIAN(1,A1,12)

 

[MartinW]

Hi Darrell

You can use Data>Validation and select whole number from the dropdown
then set between 0 and 12. Then any input other than 1 to 12 in the cell
will receive a message to try again.

HTH
Martin

 

[Guest]

TY, I like this resolution

Darrell

 

[Guest]

It appears that I have to enter data into a linked cell. I prefer to just
directly enter the value into it's own cell.

I have used the Validation option that Martin suggested.

Is there a way to enter a formula into a cell and then when it's time to
enter a value into that same cell, that the data doesn't overide the formula?

 

[Guest]

No, a cell has a formula or a constant value.