ExcelBanter - INDIRECT, OFFSET et. al.

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - INDIRECT, OFFSET et. al. (https://www.excelbanter.com/excel-worksheet-functions/19002-indirect-offset-et-al.html)

Wazooli

INDIRECT, OFFSET et. al.

How do I get the following formula:

=IF(ROW()=VLOOKUP($A2,$AY$1:$BC$49,3,FALSE),MEDIAN (INDIRECT(VLOOKUP($A2,$AY$1:$BC$49,4,FALSE) & ":" & (VLOOKUP($A2,$AY$1:$BC$49,5,FALSE)))),"")

to skip every 8th row. I am unsure as to how INDIRECT,OFFSET and ADDRESS
interact with each other. The formula above works for the first column, but
fails for every subsequent 8th column.

wazooli

Wazooli

Oops - make that skip every 8th column.

"Wazooli" wrote:

How do I get the following formula:

=IF(ROW()=VLOOKUP($A2,$AY$1:$BC$49,3,FALSE),MEDIAN (INDIRECT(VLOOKUP($A2,$AY$1:$BC$49,4,FALSE) & ":" & (VLOOKUP($A2,$AY$1:$BC$49,5,FALSE)))),"")

to skip every 8th row. I am unsure as to how INDIRECT,OFFSET and ADDRESS
interact with each other. The formula above works for the first column, but
fails for every subsequent 8th column.

wazooli

Wazooli

nevermind - got it

"Wazooli" wrote:

Oops - make that skip every 8th column.

"Wazooli" wrote:

How do I get the following formula:

=IF(ROW()=VLOOKUP($A2,$AY$1:$BC$49,3,FALSE),MEDIAN (INDIRECT(VLOOKUP($A2,$AY$1:$BC$49,4,FALSE) & ":" & (VLOOKUP($A2,$AY$1:$BC$49,5,FALSE)))),"")

to skip every 8th row. I am unsure as to how INDIRECT,OFFSET and ADDRESS
interact with each other. The formula above works for the first column, but
fails for every subsequent 8th column.

wazooli

All times are GMT +1. The time now is 12:34 PM.