Using an array with an additional independent cell value

[Guest]

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

 

[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

 

[Guest]

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)":

 

[Rick Rothstein \(MVP - VB\)]

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

=MIN(IF(A1:A4>B1,A1:A4,""))

CSE - Commit the formula using Ctrl+Shift+Enter

Rick