Hi everyone I am making a table where I want the function to subtract using the value that is lowest excluding zero in the positional sense. compared to MAX and MIN and such. The number would just be being subtracted from an original number which would be above the list of numbers.

23-original
15
13
16
10
12
0
0
0
0

in this example I want the formula to know that it is suppose to subtract 12 from 23. So the answer I should be getting would be 11.

On Tue, 14 Feb 2012 02:25:15 +0000, Gandaf wrote:


Hi everyone I am making a table where I want the function to subtract
using the value that is lowest excluding zero in the positional sense.
compared to MAX and MIN and such. The number would just be being
subtracted from an original number which would be above the list of
numbers.

23-original
15
13
16
10
12
0
0
0
0

in this example I want the formula to know that it is suppose to
subtract 12 from 23. So the answer I should be getting would be 11.

If your numbers are in column A, with "original" in A1, then try:

=A1-LOOKUP(2,1/(A:A<0),A:A)

Perfect, thank you very much

On Wed, 15 Feb 2012 20:24:57 +0000, Gandaf wrote:

If your numbers are in column A, with "original" in A1, then try:

=A1-LOOKUP(2,1/(A:A<0),A:A)


Perfect, thank you very much




--
Gandaf

Glad to help. Thanks for the feedback.