How define an Excel range in a conditional format with a range that increases
I have a spreadsheet with 1376 rows that is 6 years of daily data and with 2 columns: 1st=dates, 2nd=value. I want to do a conditional format that highlights the max value of the values for each year. I have this formula, and it works, but I want to add to the data set daily, which means I need to adjust the max row number every time I add a row of data. I would like a formula that adjusts to the size of the column automatically.
Here is my initial condition format that works, Column A are the dates. Col E is the data.
=$E2=MAX(IF(YEAR($A2)=YEAR($A$2:$A$1376),$E$2:$E$1376,””))
I could use count(A:A) to get the max row number, but how would I use that in the expression above? I tried to use the offset but I could not get that to work. Here is what I tried:
=$E2=MAX(IF(YEAR($A2)=YEAR(offset($A$2,0,0,count(A:A)+1,1)),offset($E$2,0,0,count(A:A)+1,1),””))
The +1 gets me past the title row.
Any help would be appreciated.
I have a spreadsheet with 1376 rows that is 6 years of daily data and with 2 columns: 1st=dates, 2nd=value. I want to do a conditional format that highlights the max value of the values for each year. I have this formula, and it works, but I want to add to the data set daily, which means I need to adjust the max row number every time I add a row of data. I would like a formula that adjusts to the size of the column automatically.Here is my initial condition format that works, Column A are the dates. Col E is the data.=$E2=MAX(IF(YEAR($A2)=YEAR($A$2:$A$1376),$E$2:$E$1376,””))I could use count(A:A) to get the max row number, but how would I use that in the expression above? I tried to use the offset but I could not get that to work. Here is what I tried:=$E2=MAX(IF(YEAR($A2)=YEAR(offset($A$2,0,0,count(A:A)+1,1)),offset($E$2,0,0,count(A:A)+1,1),””))The +1 gets me past the title row.Any help would be appreciated. Read More