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]

	A	B	C	D	E	F	G	H	I	J
F1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array 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] "]J1[/TH]
[TD="align: left"]{=ADDRESS(ROW(),MIN(IF(A1:H1<>0,COLUMN(A1:H1))),4)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



The question is, why do you want the cell address? If it's for inclusion in another formula, there's usually a better way to do it.

 

[excelos]

The question is, why do you want the cell address? If it's for inclusion in another formula, there's usually a better way to do it.

Yes, I want to include it in a formula. Basically, I have one row with zeros and numbers which I want to identify the most left cell with non zero value.
Then, in the row below, I want to do some other calculations and use the sum of the above identified cell and all the cells to the right up to a specific fixed cell.

Any idea?

 

[Eric W]

Well, if you can show what calculations you want to do, I might come up with something. One thought, if the start of the row is all zeros, you can certainly sum up the whole row with SUM(A1:J1), you don't need to skip the 0 cells.

 

[excelos]

Well, if you can show what calculations you want to do, I might come up with something. One thought, if the start of the row is all zeros, you can certainly sum up the whole row with SUM(A1:J1), you don't need to skip the 0 cells.

I have these two rows:

29-09-18 30-09-18 01-10-18 02-10-18 03-10-18 04-10-18
52 62 14 52 63 73

In the third row, I want a function that will compare the date in the first row and the same column, with another date in an independent cell.

If the independent date is before then fill with zero. If it is after, then I want to do this:

1) calculate the sum of the second row for the range of the cell that belongs to the column that the third row cell is the first non zero, up to the end of the second row.
2) I want then to drag that formula for all the cells in the third row, so that for each cell, it will check the date two cells above with the independent date and fill accordingly. But if the independent date is after, I want to fill with the same sum every time, which is the sum of the second row cells from the column that the third row is the first non zero, up to the end of the second row.

In fact, I want to use that sum to do some more calculations, but I can figure that out later.

Any idea? The tricky thing is how to make the formula in the third row, to scan the left cells, identify the one with the first non zero value and use that reference so that it will calculate the relevant sum in the second row.

It's so tricky! I might have to use an additional line, but if you can come up with a better idea? I am not aware of all the functions in Excel so I don't know the capabilities and limitations.

 

[Eric W]

I'm still a bit confused. Could you show what the result should be for the example above, and say what your independent date is?

 

[excelos]

It's simple, it looks like the below.

26-09-18 27-09-18 28-09-18 29-09-18 30-09-18 01-10-18 02-10-18 03-10-18
5 2 6 3 7 4 5 2
0 0 0 21 21 21 21 21


The bottom row, should contain a formula that checks the top cell of the column if it is past or after the current date. If past, fills with zero. If after, it fills with the sum of the middle line that starts from the current date up to the end of the row.

 

[Eric W]

Something like this?

Excel 2012

	A	B	C	D	E	F	G	H	I

<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]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B3[/TH]
[TD="align: left"]=IF(A3<>0,A3,IF(B1< TODAY(),0,SUM(B2:$I2)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Put the formula in B3 and drag to the right. I left an empty column in A for simplicity, but we can change that. The results are different from your example since we've moved a day ahead.

 

[excelos]

Something like this?

Excel 2012

	A	B	C	D	E	F	G	H	I

<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]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="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]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Put the formula in B3 and drag to the right. I left an empty column in A for simplicity, but we can change that. The results are different from your example since we've moved a day ahead.

Thanks

Can you explain what that does?
The first bit checks the left cell and if it is non zero, it fills it with the same value. That's not something that I want. I want to fill with zero or non zero, based on the top date in respect to the current date.

Your formula then checks for the date and it sums B2:I2.
I am not sure what this sum is.

Any explanation please?

 

[excelos]

Also, can you make it, when the date is today or after, to calculate the percentage of the two cells above value to the specified sum, ie the sum of the second row values, from the first cell that the below cell is non zero up to the end of the row.

thanks!