How to use a BLANK in a complex formula
Posted on 2014-12-15
The Excel table is setup as follows
Across the top there can be up to 6 territories
Each territory has two columns (one is a count column and the other is a Win column)
Down the side is a row for each month.
The variables are that there could be anywhere from 2 to 6 territories active for that sheet.
The objective is to get the WIN column for the one that has a BLANK value in the Count column display a value of 1. So if there are only 2 territories naturally the one that has no value in the count column will have the value of 1 in the win column. But when you start adding 3, then 4 and up to 6 territories the formula because a lot more complex. If there is no territory name then there should be no values in the Win column at all. There can be a value of ZERO in any of the columns as this is a reportable value. ONLY a BLANK can get a Win value.
Could really use some suggestions on how to get around this.