| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
I want to limit the result of a calculation to a maximum number...
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I want to limit the result of a calculation to a maximum number...
=IF(B4=0,0,IF(B6=0,0,MIN(600,B6/(PI()*(B4/12)))))
-- David Biddulph "Dr. Darrell" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I want to limit the result of a calculation to a maximum number...
Hi Darrel,
=MIN(IF(B4=0,0,IF(B6=0,0,B6/(PI()*(B4/12)))),600) -- Kind regards, Niek Otten Microsoft MVP - Excel "Dr. Darrell" wrote in message ... |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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I want to limit the result of a calculation to a maximum numbe
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 "Niek Otten" wrote: Hi Darrel, =MIN(IF(B4=0,0,IF(B6=0,0,B6/(PI()*(B4/12)))),600) -- Kind regards, Niek Otten Microsoft MVP - Excel "Dr. Darrell" wrote in message ... |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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I want to limit the result of a calculation to a maximum numbe
If you want to contrain a value to be between 1 and 12, you can either use
=IF(A1<1,1,IF(A112,12,A1)) or =MAX(1,MIN(A1,12)) or =MEDIAN(1,A1,12) -- David Biddulph "Dr. Darrell" wrote in message ... 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 "Niek Otten" wrote: Hi Darrel, =MIN(IF(B4=0,0,IF(B6=0,0,B6/(PI()*(B4/12)))),600) -- Kind regards, Niek Otten Microsoft MVP - Excel "Dr. Darrell" wrote in message ... |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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I want to limit the result of a calculation to a maximum numbe
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? "David Biddulph" wrote: If you want to contrain a value to be between 1 and 12, you can either use =IF(A1<1,1,IF(A112,12,A1)) or =MAX(1,MIN(A1,12)) or =MEDIAN(1,A1,12) -- David Biddulph "Dr. Darrell" wrote in message ... 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 "Niek Otten" wrote: Hi Darrel, =MIN(IF(B4=0,0,IF(B6=0,0,B6/(PI()*(B4/12)))),600) -- Kind regards, Niek Otten Microsoft MVP - Excel "Dr. Darrell" wrote in message ... |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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I want to limit the result of a calculation to a maximum numbe
No, a cell has a formula or a constant value.
-- Regards, Tom Ogilvy "Dr. Darrell" wrote: 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? "David Biddulph" wrote: If you want to contrain a value to be between 1 and 12, you can either use =IF(A1<1,1,IF(A112,12,A1)) or =MAX(1,MIN(A1,12)) or =MEDIAN(1,A1,12) -- David Biddulph "Dr. Darrell" wrote in message ... 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 "Niek Otten" wrote: Hi Darrel, =MIN(IF(B4=0,0,IF(B6=0,0,B6/(PI()*(B4/12)))),600) -- Kind regards, Niek Otten Microsoft MVP - Excel "Dr. Darrell" wrote in message ... |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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I want to limit the result of a calculation to a maximum numbe
Hi Darrell
You can use DataValidation 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I want to limit the result of a calculation to a maximum numbe
TY, I like this resolution
Darrell "MartinW" wrote: Hi Darrell You can use DataValidation 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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I want to limit the result of a calculation to a maximum number...
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding maximum qty of items to reach given limit | Excel Worksheet Functions | |||
| Formula for calculating a running balance up to a maximum limit | Excel Worksheet Functions | |||
| Excel Solver (Maximum Limit of no. of variables & constraints) | Excel Discussion (Misc queries) | |||
| Formula to limit the size of a number result in a cell? | Excel Discussion (Misc queries) | |||
| How do I change - increase maximum limit of the row height (409 p. | Excel Discussion (Misc queries) | |||
Hybrid Mode
