Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
SKip past fields with no data
I am trying to build a formula that reference cells in a table. My goal is to only return values that have data in the linear feet and weight in Table1. So if a cell in the table is blank don't return it. Any ideas on how to fix?
=IFERROR(INDEX(Table1[SCAC],SMALL(IF((Table1[Linear Feet]>=$B$4)*(Table1[Weight]>=$CS$4),ROW(Table1[SCAC])-MIN(ROW(Table1[SCAC]))+1),ROWS(E$3:E3))),"")
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Just edit above formula with this one:
Carrier-Volume-Rate-Estimate_v1.xlsm
=IF(B4="","",IFERROR(INDEX(Table1[SCAC],SMALL(IF((Table1[Linear Feet]>=$B$4)*(Table1[Weight]>=$CS$4),ROW(Table1[SCAC])-MIN(ROW(Table1[SCAC]))+1),ROWS(E$3:E3))),""))
Carrier-Volume-Rate-Estimate_v1.xlsm
You can also have below formula, either B4 or C4 is blank, it will result blank:
=IF(OR(B4="",C4=""),"",IFERROR(INDEX(Table1[SCAC],SMALL(IF((Table1[Linear Feet]>=$B$4)*(Table1[Weight]>=$CS$4),ROW(Table1[SCAC])-MIN(ROW(Table1[SCAC]))+1),ROWS(E$3:E3))),""))
Will this formula if linear feet or the weight is greater than or equal to the reference cell return the value
Premium Content
You need an Expert Office subscription to comment.Start Free Trial