Web Applications Asked by jlo on December 18, 2020
I have a sheet with a table, where I want to extract the header of the max value in each row. The simple solution for that is:
=IFERROR(IF(AND(B2<>"Sa",B2<>"Su"),INDEX($C$1:$Y$1,MATCH(MAX(C2:Y2),C2:Y2,0)),"weekend"),"")
Problem is, that if you insert a column to the right on column Y, the formula will not change the range (it will still be C2:Y2, and data on column Z will not be included). For that I created this formula, which solves this issue:
=iferror(if(AND(B2<>"la";B2<>"su");INDEX($C$1:$1;MATCH(MAX(INDIRECT("C"&row()&":"&SUBSTITUTE(ADDRESS(1;MATCH(OFFSET(INDIRECT(ADDRESS(1;COLUMN()));0;-5);$1:$1;0)-1;4);1;"")&row()));INDIRECT("C"&row()&":"&SUBSTITUTE(ADDRESS(1;MATCH(OFFSET(INDIRECT(ADDRESS(1;COLUMN()));0;-5);$1:$1;0)-1;4);1;"")&row());0));"w");"")
I was wondering: is there an easier/simpler way to do this with google sheets formulas? Basically I want the range to be from C1:<whatever letter is before the Total column>
.
Thanks!
Please try the following:
=IFERROR(IF(AND(B2<>"Sa",B2<>"Su"),INDEX($C$1:$1,MATCH(MAX(INDIRECT("C"&ROW()&":"&SUBSTITUTE(ADDRESS(1,MATCH(INDIRECT(ADDRESS(1,iferror(match("Total",$A$1:$1,0)-1,"No match"))),$1:$1,0)-1,4),1,"")&ROW())),INDIRECT("C"&ROW()&":"&SUBSTITUTE(ADDRESS(1,MATCH(INDIRECT(ADDRESS(1,iferror(match("Total",$A$1:$1,0)-1,"No match"))),$1:$1,0)-1,4),1,"")&ROW()),0)),"weekend"),"")
What we do is replace the
part of the formula withOFFSET(INDIRECT(ADDRESS(1,COLUMN())),0,-5)
INDIRECT(ADDRESS(1,iferror(match("Total",$A$1:$1,0)-1,"No match")))
INDIRECT(ADDRESS(1,iferror(match("Total",$A$1:$1,0),"No match")))
will always return the column Total
no matter where placed.
By adding -1
it will return the column before Total
, thus eliminating the need for the the OFFSET
function.
Answered by marikamitsos on December 18, 2020
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP