Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula for range values question
I'm not the most savvy Excel user, so this may be something I'm trying to do that's over my head, but I'm willing to learn as best I can. I'll try to be as clear as possible on this one. The formula that I'm trying to accomplish sets limits min/max to the final value of a number that is outputted. So, if the final output is 100, but based on a set of rules, the value's max is only 90, then the formula will only allow the final output to be 90....same for the reverse, if the final output is 20, but the minimum allowed output is 50, then the final output will show the number 50. Example: I have a set of value settings: Variable setting -- Min -- max restrictions 1 -- 0 --10 2 -- 5 -- 15 3 -- 8 -- 20 4 -- 10 -- 30 5 -- 20-- 60 Case1: final output is 30, but within the parameters it is set to Variable#3, so...the highest number possible is 20....thus, the output becomes 20 rather than30. Case2: the final output is 10, but within the parameters, it's set to Variable#5. The lowest value there is 20, so the output then becomes 20 rather than 10, because of the restrictions. If I'm making no sence, let me know, I'll try to be more clear. Thanks for the help in advance. Owen -- webadict2be ------------------------------------------------------------------------ webadict2be's Profile: http://www.excelforum.com/member.php...o&userid=31236 View this thread: http://www.excelforum.com/showthread...hreadid=509023 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula for range values question
So, presumably you have a "Variable Type" column and an "Outcome"
column - assume these are A and B with the values starting in row 2. Assume the table above - Variable, Min, Max - is in the same sheet occupying X1 to Z6 including headings. Try this formula: =IF(OR(A25,A2<0),"Invalid variable",IF(B2<VLOOKUP(A2,$X$2:$Z$6,2,0),VLOOKUP( A2,$X$2:$Z$6,2,0),IF(B2VLOOKUP(A2,$X$2:$Z$6,3,0), VLOOKUP(A2,$X$2:$Z$6,3,0),B2))) Copy down as necessary. Hope this helps. Pete |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula for range values question
Another way:
the following in A1:F8 variable 1 2 3 4 5 min 0 5 8 10 20 max 10 15 20 30 60 output 15 13 30 20 10 params 1 2 3 4 5 result 10 13 20 20 20 Formula in B8: =IF(B5<=INDEX($B$1:$F$3;2;B6);INDEX($B$1:$F$3;2;B6 );IF(B5=INDEX($B$1:$F$3;3;B6);INDEX($B$1:$F$3;3;B 6);B5)) Copied to the right to F8. Hans |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula for range values question
Don Guillett Wrote: have a look in the help index for functions CEILING & FLOOR I'll take a look at this as with all the other input you guys have been giving me. The problem with the help index, was I didn't know what it would be called, so this gives me a good idea. I'll try to run the formulas that you guys gave me to see how this works out. Thanks, Owen -- webadict2be ------------------------------------------------------------------------ webadict2be's Profile: http://www.excelforum.com/member.php...o&userid=31236 View this thread: http://www.excelforum.com/showthread...hreadid=509023 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
SUM formula - using variable values | Excel Worksheet Functions | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
copy values generated by conditional formula in one sheet to the other work sheet as values | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions |