Apr 24 2019 09:45 AM
Hello,
Is there any way to change the absolute reference value into a new one automatically whenever copy into a new location.
e.g. There is a series of number from A1:A5 and I want to find out highest value on adjacent to the cell like =IF(((MAX($A$1:$A$5)=A1),A1,0) and drag down so this will either give me only highest value and 0 in B1 to B5.
Now my question is if I want to use the same type of formula in another location then I have to keep changing the particular value of MAX from the equation.
Is there any way to change that absolute reference value with new absolute value.
Thank you
Apr 24 2019 10:12 AM
Apr 24 2019 10:56 AM
Apr 24 2019 11:12 AM
Apr 24 2019 11:43 AM
@Twifoo Sorry this is the first time in community so I didn't had an idea about the procedure to solve the method. I have explained my problem in excel file itself. Please do check and help to solve the problem.
Thank you
Apr 24 2019 01:47 PM
Straigthforward solution could be
=IF((MAX(INDEX($A$1:A1000,IFERROR(AGGREGATE(14,6,1/($A$1:A1="")*ROW($A$1:A1),1)+1,1)):INDEX(A1:A1000,MATCH(1,INDEX(--(A1:A1000=""),0),0)-1))=A1),A1,0)
CSE formula
Apr 24 2019 10:36 PM
In the attached file, the formula in B1, copied down rows, is:
=(MAX(INDEX(A$1:A2,
IFERROR(AGGREGATE(14,6,1/(A$1:A1="")*ROW(A$1:A1),1)+1,1)):
INDEX(A1:A10,
MATCH(1,INDEX(--(A1:A10=""),0),0)-1))=A1)*A1
Note that the foregoing formula follows a logic similar to that of @Sergei Baklan . Nonetheless, it is a non-array formula and assumes that the maximum number of cells to be evaluated at any given instance is 9. If, for example, such maximum is 99, A10 will have to be modified to A100, as follows:
=(MAX(INDEX(A$1:A2,
IFERROR(AGGREGATE(14,6,1/(A$1:A1="")*ROW(A$1:A1),1)+1,1)):
INDEX(A1:A100,
MATCH(1,INDEX(--(A1:A100=""),0),0)-1))=A1)*A1
By the way, Sergei's formula assumes such maximum to be 999.
Apr 25 2019 04:18 AM
Reading @Twifoo post I corrected a bit the formula to drop limitation
1) Actually all formulas are regular (not array ones), includes initial
2) A1:A1000 could be changed on A1:A$1048576, but that's performance
=IF((MAX(INDEX(A:A,IFERROR(AGGREGATE(14,6,1/($A$1:A1="")*ROW($A$1:A1),1)+1,1)):INDEX(A1:A$1048576,MATCH(1,INDEX(--(A1:A$1048576=""),0),0)-1))=A1),A1,0)
Apr 25 2019 04:28 AM
Apr 25 2019 05:09 AM
@chavanpr12 , if format a bit and take the formula for the first cell of the second range
=IF((MAX( INDEX(A:A, IFERROR(AGGREGATE(14,6,1/($A$1:A10="")*ROW($A$1:A10),1)+1,1)): INDEX(A10:A$1048576, MATCH(1,INDEX(--(A10:A$1048576=""),0),0)-1)) =A10),A10,0)
AGGREGATE returns the first (4th parameter 1) largest (first parameter 14) value in the array (3rd parameter) ignoring all errors (second parameter 6).
Our array here is multiplication of criteria 1/($A$1:$A10="") on rows numbers from $A$1 to current one. Criteria returns an error for all blank cells (which will be ignored) and 1 (=1/TRUE) for all non-blank cells. We multiply on cell's row numbers and return the largest one, in our case 10. IFERROR is needed for the beginning of the first range which starts from A1.
Now as result we have INDEX(A:A,10), or 10th cell in the column A, or A10.
Within MATCH the INDEX(--(A10:A$1048576=""),0) returns an array with TRUE for each blank cell in the range starting from A10 till end of the column and FALSE otherwise, double dash converts them to 1 and 0. MATCH find position of the first number 1 (1st parameter) in this array, other words position of the first blank cell, in our case that will be 6. Minus 1 gives position of last non-blank cell (5).
Thus second INDEX will be INDEX(A10:A$1048576,5) which returns 5th cell in above range, or A14.
As result INDEX(...):INDEX() will be equivalent of A10:A14 and we apply MAX to that range.
Apr 25 2019 05:14 AM
Apr 25 2019 05:53 AM
@Twifoo , thank you