Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to I set a limit on the value returned by a cell
I need to set a limit on a cell, whereas it will limit the amount from one
formula and add the value to the set limit from another cell. For instance I want the maximum input from cell "A" to be limited to a certain quantity, yet allow the value from cell "B" to be added to the maximum quantiy from cell "A" being displayed in cell "C". Example, input from cell "A" call it "A1" whereas A1 can = 0 through 1000, and cell "B" call it "A2" can = 0 through 50, and cell "C" being "A3" is the sum of "A1" and "A2", I need to limit the input from "A1*5" to 500 but I also need to add the total in "A2" to "A3" with no restrictions whereas "A3" can only accept a maximum amount from "A1", but accept any number from "A2". So if "A1" equals 750 the maximum return to "A3" of (A1*5) cannot exceed 500, while the sum of (A1*5)+A2 can be greater than 500. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to I set a limit on the value returned by a cell
One way:
A3: =MAX(A1,100)*5 + A2 In article , WavMaster wrote: I need to set a limit on a cell, whereas it will limit the amount from one formula and add the value to the set limit from another cell. For instance I want the maximum input from cell "A" to be limited to a certain quantity, yet allow the value from cell "B" to be added to the maximum quantiy from cell "A" being displayed in cell "C". Example, input from cell "A" call it "A1" whereas A1 can = 0 through 1000, and cell "B" call it "A2" can = 0 through 50, and cell "C" being "A3" is the sum of "A1" and "A2", I need to limit the input from "A1*5" to 500 but I also need to add the total in "A2" to "A3" with no restrictions whereas "A3" can only accept a maximum amount from "A1", but accept any number from "A2". So if "A1" equals 750 the maximum return to "A3" of (A1*5) cannot exceed 500, while the sum of (A1*5)+A2 can be greater than 500. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to I set a limit on the value returned by a cell
That still returns more than the specified amount from "A1"
IE: the sum for the following should be 501 but the return is still 506 in as much as "A1" equals 101, I need it's value in "A3" to be limited to 100*5, in stead of being returned as 505, additionally the value from "A2" which is 1 needs to be added to the maximum accepted amount of 500 from "A1", hench [(A1*5)+A2] can only equal 501and if "A1" equals 250 times 5, it can still only calculate at 500 for this cell value where "A2" can be unlimited in it's result, so if "A1" times 5 equals 1250, it's value can only be reported as 500 but if "A2" equals 10 the displayed value in that senerio the value in "A3" should still calculate at only 510, I know it is possible to do this, I just can't make it work right now Thanks "JE McGimpsey" wrote: One way: A3: =MAX(A1,100)*5 + A2 In article , WavMaster wrote: I need to set a limit on a cell, whereas it will limit the amount from one formula and add the value to the set limit from another cell. For instance I want the maximum input from cell "A" to be limited to a certain quantity, yet allow the value from cell "B" to be added to the maximum quantiy from cell "A" being displayed in cell "C". Example, input from cell "A" call it "A1" whereas A1 can = 0 through 1000, and cell "B" call it "A2" can = 0 through 50, and cell "C" being "A3" is the sum of "A1" and "A2", I need to limit the input from "A1*5" to 500 but I also need to add the total in "A2" to "A3" with no restrictions whereas "A3" can only accept a maximum amount from "A1", but accept any number from "A2". So if "A1" equals 750 the maximum return to "A3" of (A1*5) cannot exceed 500, while the sum of (A1*5)+A2 can be greater than 500. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to I set a limit on the value returned by a cell
Oops-
Should have been MIN() instead of MAX()... In article , WavMaster wrote: That still returns more than the specified amount from "A1" IE: the sum for the following should be 501 but the return is still 506 in as much as "A1" equals 101, I need it's value in "A3" to be limited to 100*5, in stead of being returned as 505, additionally the value from "A2" which is 1 needs to be added to the maximum accepted amount of 500 from "A1", hench [(A1*5)+A2] can only equal 501and if "A1" equals 250 times 5, it can still only calculate at 500 for this cell value where "A2" can be unlimited in it's result, so if "A1" times 5 equals 1250, it's value can only be reported as 500 but if "A2" equals 10 the displayed value in that senerio the value in "A3" should still calculate at only 510, I know it is possible to do this, I just can't make it work right now Thanks "JE McGimpsey" wrote: One way: A3: =MAX(A1,100)*5 + A2 In article , WavMaster wrote: I need to set a limit on a cell, whereas it will limit the amount from one formula and add the value to the set limit from another cell. For instance I want the maximum input from cell "A" to be limited to a certain quantity, yet allow the value from cell "B" to be added to the maximum quantiy from cell "A" being displayed in cell "C". Example, input from cell "A" call it "A1" whereas A1 can = 0 through 1000, and cell "B" call it "A2" can = 0 through 50, and cell "C" being "A3" is the sum of "A1" and "A2", I need to limit the input from "A1*5" to 500 but I also need to add the total in "A2" to "A3" with no restrictions whereas "A3" can only accept a maximum amount from "A1", but accept any number from "A2". So if "A1" equals 750 the maximum return to "A3" of (A1*5) cannot exceed 500, while the sum of (A1*5)+A2 can be greater than 500. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Limit returned value | Excel Worksheet Functions | |||
Limit returned value to last digit | Excel Worksheet Functions | |||
limit characters returned a cell reference | Excel Worksheet Functions | |||
cell contents returned | Excel Worksheet Functions | |||
blank cell returned | Excel Worksheet Functions |