Change the absolute reference into new one whenever copy to new location

Copper Contributor

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

11 Replies
Use mixed reference like this in B1, copied down to B5:
=(MAX(A$1:A$5)=A1)*A1
Ya that one i got but if i copy that formula then the row is going to be fixed and it won't change the to other cell like A$10:A$15 automatically.
I hope I get help with that if possible
That’s just a sample structure of the formula I suggest you to use. For specifics, you need to attach your sample file. Remember, no one could possibly imagine the structure of your actual data, without a sample file. You need not think twice to believe me!

@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

@chavanpr12 ,

 

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

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

@Sergei Baklan ,

 

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)

 

@sergei_Baklan All this is difficult formula and hard to comprehend so is it possible to explain each term and their effect so I can edit according in my actual file.
Thank you.

@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.