I want to calculate the kth smallest number of an array, but the formula must
take the array values by subtracting a constant from each array value. I know
you can tell me to design a new array by subtracting a constant from each
array value, but i wonder whether i can skip an additional column.

The formula below is WRONG but can express what i meant to do:
=SMALL(({A1-B1;A2-B1;A3-B1;A4-B1});3)

Thanks for help

Unless I am missing something from your question... if you are subtracting
the same constant value from each array element, wouldn't the Kth smallest
element stay the same? Just get the Kth smallest value from the original
array and subtract your constant from whichever value is returned. Based on
your posted test formula, I am thinking you want this...

=SMALL(A1:A4,3)-B1

Rick


"nerohnze" wrote in message
...
I want to calculate the kth smallest number of an array, but the formula
must
take the array values by subtracting a constant from each array value. I
know
you can tell me to design a new array by subtracting a constant from each
array value, but i wonder whether i can skip an additional column.

The formula below is WRONG but can express what i meant to do:
=SMALL(({A1-B1;A2-B1;A3-B1;A4-B1});3)

Thanks for help

Thanks Rick,
You are right, I transformed my originial question to be more simple, but i
coudn't express it true.

Here is the more like original question:
I have an array(A1:A4) and the formula is :

=SMALL(A1:A4;COUNTIF(A1:A4;"<0")+1)
This means i want to reach the smallest positive number in the array.

Say that the A1, A2, A3 and A4 are 3, 5, 7 and 8 sequently.
The answer is 3.
But if i subtract a constant like "4" from A1, A2, A3, A4;
The answer is now 5, because: 5-4=1 the smallest positive number.

There may be some other ways to solve, or i may be missing a simple
solution, but i could't fix it.

Thanks for help

"Rick Rothstein (MVP - VB)":

Unless I am missing something from your question... if you are subtracting
the same constant value from each array element, wouldn't the Kth smallest
element stay the same? Just get the Kth smallest value from the original
array and subtract your constant from whichever value is returned. Based on
your posted test formula, I am thinking you want this...

=SMALL(A1:A4,3)-B1

Rick


"nerohnze" wrote in message
...
I want to calculate the kth smallest number of an array, but the formula
must
take the array values by subtracting a constant from each array value. I
know
you can tell me to design a new array by subtracting a constant from each
array value, but i wonder whether i can skip an additional column.

The formula below is WRONG but can express what i meant to do:
=SMALL(({A1-B1;A2-B1;A3-B1;A4-B1});3)

Thanks for help

The problem as you now state it can be solved with this CSE formula...

=MIN(IF(A1:A4B1,A1:A4,""))

CSE - Commit the formula using Ctrl+Shift+Enter

Rick


"nerohnze" wrote in message
...
Thanks Rick,
You are right, I transformed my originial question to be more simple, but
i
coudn't express it true.

Here is the more like original question:
I have an array(A1:A4) and the formula is :

=SMALL(A1:A4;COUNTIF(A1:A4;"<0")+1)
This means i want to reach the smallest positive number in the array.

Say that the A1, A2, A3 and A4 are 3, 5, 7 and 8 sequently.
The answer is 3.
But if i subtract a constant like "4" from A1, A2, A3, A4;
The answer is now 5, because: 5-4=1 the smallest positive number.

There may be some other ways to solve, or i may be missing a simple
solution, but i could't fix it.

Thanks for help

"Rick Rothstein (MVP - VB)":

Unless I am missing something from your question... if you are
subtracting
the same constant value from each array element, wouldn't the Kth
smallest
element stay the same? Just get the Kth smallest value from the original
array and subtract your constant from whichever value is returned. Based
on
your posted test formula, I am thinking you want this...

=SMALL(A1:A4,3)-B1

Rick


"nerohnze" wrote in message
...
I want to calculate the kth smallest number of an array, but the formula
must
take the array values by subtracting a constant from each array value.
I
know
you can tell me to design a new array by subtracting a constant from
each
array value, but i wonder whether i can skip an additional column.

The formula below is WRONG but can express what i meant to do:
=SMALL(({A1-B1;A2-B1;A3-B1;A4-B1});3)

Thanks for help