ExcelBanter - formula for range values question

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - formula for range values question (https://www.excelbanter.com/excel-discussion-misc-queries/69845-formula-range-values-question.html)

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

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

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

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