Is there some way to write a function to find the maximum or minimum of a set of values without using the max or min functions, and no if statements can be used?

*and it has to be a linear function

[Edited on November 13, 2007 at 7:53 PM. Reason : .]

11/13/2007 7:53:06 PM

why wouldn't you use min or max

11/13/2007 8:40:49 PM

because they techinically aren't linear functions, according to my professor

11/13/2007 8:43:12 PM

graph them then draw an arrow pointing to the lowest one

[Edited on November 13, 2007 at 9:05 PM. Reason : maybe you can cheat and use a ternary operator, that's not precisely an if]

11/13/2007 8:56:41 PM

Nm

[Edited on November 13, 2007 at 9:03 PM. Reason : c]

11/13/2007 9:01:51 PM

paging LimpyNuts

11/13/2007 9:27:19 PM

help:

http://www.cob.sjsu.edu/anaya_j/LinPro.htm

?

11/13/2007 9:30:50 PM

i don't know about the linear function part, but if your data is in list form, this will work for maximum:


assuming the first entry is in cell A2:

=(A2*(AND((A2>A3),(A2>A4))))+(A3*(AND((A3>A2),(A3>A4))))+(A4*(AND((A4>A3),(A4>A2))))+...

all it does is use boolean 'AND' to check the value in the cell to see if its larger than all the rest of the values. if its a long list of numbers, you may have to nest the ANDs or something, i'm not sure how many arguments excel can compare at once. you could probably do the same sort of thing for minimum, just make it check to see if the value is smaller than all the other values.

i'm sure someone will come up with a cleaner way to do it, but it doesn't use ifs or the max/min functions

[Edited on November 14, 2007 at 12:22 AM. Reason : .]

11/14/2007 12:19:36 AM

What the hell is the question?

What do you mean by "write a function"? Are you talking about using VBA to make a new worksheet function?

Or are you talking about an expression (the stuff you type into a worksheet cell to calculate something)?

What is a "linear function"? The definition of a linear function is a linear map between 2 vector spaces that preserves vector addition and scalar multiplication.

MAX(A + B) = MAX(A) + MAX(B)
MAX(a*A) = a *MAX(A)

MAX and MIN are not linear functions in this respect; however, nothing you can do will change that. MIN and MAX are not linear functions. Of course, if you sort vectors A and B then the property will be preserved (but only in the special case where the min/max values are in the same vector index). But what the hell is the use in that?

What are you trying to do?

11/14/2007 8:50:43 AM

sorry, I meant write an expression. but I figured it out. thanks guys.

i'm using solver to minimize the maximum value of a calculated set of numbers. It's tough to explain if you don't know much about groundwater/aquifers. But one constraint on us, is that it must be linear, thus if's, max, min functions are a no go.

thanks

11/15/2007 9:31:02 AM

Im a noob in excel so forgive me if this makes no sense. I am trying to set up a spreadsheet that takes a given value and subtracts a column of numbers from it. The problem is I want to subtract the the sum of the number in the column and every number above it. Is there any way to "drag" a expression to do this?

11/18/2007 4:53:27 PM

It's not a problem, but I don't understand exactly what you're trying to do.

10   1   9
10   2   7
10   3   4
10   4   0

11/18/2007 7:11:11 PM

haha thanks alot, I was just being slow

11/18/2007 8:12:41 PM