Re: Using an array with an additional independent cell value
- From: "Rick Rothstein \(MVP - VB\)" <rickNOSPAMnews@xxxxxxxxxxxxxxxxx>
- Date: Mon, 15 Oct 2007 05:26:57 -0400
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
"nerohnze" <nerohnze@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:D76ED09F-4F9F-41C8-8EA0-F6B86EF28E0D@xxxxxxxxxxxxxxxx
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" <nerohnze@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:79F8AD35-2F07-47D3-9B3A-5DC02DFCBD50@xxxxxxxxxxxxxxxx
>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
.
- References:
- Re: Using an array with an additional independent cell value
- From: Rick Rothstein \(MVP - VB\)
- Re: Using an array with an additional independent cell value
- Prev by Date: Re: run time 1004: the method 'applycustomtype' for object '_chart
- Next by Date: Re: Problem with Excel Charting ... Please Help
- Previous by thread: Re: Using an array with an additional independent cell value
- Next by thread: Re: macro to insert formula if cell format criteria satisafied
- Index(es):
Relevant Pages
|
