Variable reference to noncontiguous cells...
Hi,
Am Thu, 30 Jan 2020 18:56:19 -0600 schrieb dpb:
="Average last 5 years
="&TEXT(AVERAGE(IF(MOD(COLUMN(OFFSET(AS7,0,-24):OFFSET(AS7,0,-8)),4)=1,OFFSET(AS7,0,-24):OFFSET(AS7,0,-8))),"
$#,##0. ") &"Range =
"&TEXT(MIN(IF(MOD(COLUMN(OFFSET(AS7,0,-24):OFFSET(AS7,0,-8)),4)=1,OFFSET(AS7,0,-24):OFFSET(AS7,0,-8))),"
$#,##0 ")&"-"
&TEXT(MAX(IF(MOD(COLUMN(OFFSET(AS7,0,-24):OFFSET(AS7,0,-8)),4)=1,OFFSET(AS7,0,-24):OFFSET(AS7,0,-8))),"
$#,##0. ")
OFFSET has two more arguments. You can use them to shorten the formula.
If all your cells are filled and you want average the range from column
U to the last column try:
=AVERAGE(IF(MOD(COLUMN(OFFSET(U7,,,,COUNTA(U7:ZZ7) )),4)=1,OFFSET(U7,,,,COUNTA(U7:ZZ7))))
Regards
Claus B.
--
Windows10
Office 2016
|