Find non-empty cell in row and apply formula to that cell only

**mini_dutch28** · 03-20-2013, 08:15 PM

Hi,

I have a table with 12 columns and 800 odd rows, each row only has one value in it situated in any of the 12 columns. Most of the values are made up of multiple parts (eg =45+56 or =12+89+13 etc) and so I have a formula to find how many parts are in each non-blank cell.
The formula is [=LEN(Frmula)-LEN(SUBSTITUTE(Frmula,"+",""))+1] the "Frmula" being an XLM 4 macro to turn the cell to the left into a text string which allows the Len and Substitute formulas to return the number of plus signs in the cell.

Is it possible to find the non-empty cell in the row and then apply the above formula to it to return the number of parts in the cell?

For example, if the only value in the table row is in column 3, then i need a formula to find that the only value in the row is in column 3 and then imbed my formula above to return the result i am looking for.

I was thinking of using a Match/Index function, but I'm not sure how i can imbed my formula to produce the result.

Cheers

**majosum** · 03-20-2013, 08:26 PM

I just created a mock row in Excel from column A to C, so you will need to change the row/column references to fit your wksh.

=MATCH(SUM(A1:C1),A1:C1,0)

**mini_dutch28** · 03-20-2013, 08:35 PM

Hi majosum,

the formula you have provided above gives me the column number of the non-empty cell in the row.

how do i use this to apply another formula to the value at the column reference?

for example, if my value is in column 3, and the above formula you have provided returns a value of 3, how do i now go about applying a formula to the value at that reference point?

**majosum** · 03-20-2013, 08:52 PM

In what column is the =LEN() formula and what do you mean it turns the cell to the left into a text string?

**mini_dutch28** · 03-20-2013, 09:07 PM

currently the Len formula only works if it is situated in the cell to the right of the value; so if the value was in cell B1, the len formula would be in cell C1.

the full formula i have is below:

=LEN(GET.CELL(6,OFFSET(INDIRECT("RC",FALSE),0,-1)))-LEN(SUBSTITUTE(GET.CELL(6,OFFSET(INDIRECT("RC",FALSE),0,-1)),"+",""))

the (GET.CELL(6,OFFSET(INDIRECT("RC",FALSE),0,-1)) part of the formula turns the cell to the left into a text string, which then allows the Len and substiture formulas to find how many plus signs are in the formula. if it was not turned into a text string, the len and substitute formulas wouldnt be able to return the correct number of plus signs in the cell.

the issue i face is the fact that i cant manually input the formula in the cell to the right of the value as it would take forever as there are almost 1000 rows, thus i am trying to find a formula, such as your MATCH formula above to find the position of the value in the row and apply my formula described above.

**majosum** · 03-20-2013, 09:23 PM

That formula looks confusing, but if I am not mistaken, the "-1" in the offset is used to look to the left one cell right? Instead of using -1, see if you can substitute the following portion for the -1 and place the entire formula in the 13th column and drag down. Be sure to keep that negative sign at the beginning and once again, you'll need to change the cells to match your worksheet.

-(COLUMN(D1)-MATCH(SUM(A1:C1),A1:C1,0))

**majosum** · 03-20-2013, 09:28 PM

Sorry, I forgot to say. D1 was the cell that I typed the formula into.

**mini_dutch28** · 03-20-2013, 10:32 PM

so i used part of your formula and it has worked, however i did modify it a bit.

when using the match formula it counts from the left, but the formula i was using was counting backwards from the right, so the cell being referenced was empty. so instead of the column(D1) part, i substituted the -1 at the end of my Offset formula to the following: -(13-match(sum(E6:P6),E6:P6,0)) this using the value from the match formula (eg 3) and counts back 10 cells (13-3) to the 3rd cell to return the correct value.

thanks for your help!