Is it possible to identify a cell and use its co-ordinates?

[excelos]

Hello

Is it possible to have a formula that will check all the cells at left in the row, find the first cell that is not zero, and use its co-ordinates (eg A12) ?

Thanks!

 

[Eric W]

Excel 2012

	A	B	C	D	E	F	G	H	I

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9/26/2018[/TD]
[TD="align: right"]9/27/2018[/TD]
[TD="align: right"]9/28/2018[/TD]
[TD="align: right"]9/29/2018[/TD]
[TD="align: right"]9/30/2018[/TD]
[TD="align: right"]10/1/2018[/TD]
[TD="align: right"]10/2/2018[/TD]
[TD="align: right"]10/3/2018[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]18[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"]39%[/TD]
[TD="align: right"]22%[/TD]
[TD="align: right"]28%[/TD]
[TD="align: right"]11%[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]=IF(A3<>0,A3,IF(B1< TODAY(),0,SUM(B2:$I2)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4[/TH]
[TD="align: left"]=IF(B3=0,"",B2/B3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



As I understand your requirements, the B3 formula does exactly what you want, although I took a different angle. Since the value from F3 to I3 is the same every time, we only need to calculate it once, in F3. So the formula first checks to see if we have calculated it already, if so, we just copy it from the cell to the left. If the cell to the left is 0 (or empty), then we need to check the date. If the date is less than today, we put 0. If equal to or greater than today, then we sum up the values from the current column to the end of the range (note the $ in the last range of the formula).

The B4 formula should calculate the percentage you want.