Help please

[bernie3]

I am new to complex formulas and would appreciate some help. I am importing data from another application. I have a series of rows and columns containing data. Not all cells contain data but the last cell with data represents the sum of the data in the cells on the same row to the left of this cell.

I am wanting to have this total (last cell containing data) to be put into a new cell called ( in a new column called "total" and the data in last cell to be deleted. Basically, I need to recognise the last cell to the left of the column called "total" that has a data value and get this data in the cell containing the formula.


Thanks for your help

 

[Hui]

Bernie

I assume you have data like

1 2 3 6

10 20 30 40 50 150

3 3 6

2 2 2 2 2 2 2 2 2 2 20

etc

ie: a different number of columns in each row

a few ideas, assuming all values are positive

1. Goto a column which is to the right of the furthest right data lets use AA

2. Use a formula in AA1 like

=+if(A2="","",A1)

3. Copy it across and then copy the formulas in that row down

4. Copy the entire area from Column AA to AZ and paste as values

5. Go to the column to the right of new copied area say Column BA and put in a new "Total" Column with a formula =+sum(AA1:AZ1)

6. Copy down to the bottom of the data

7. You can now delete columns A:Z

 

[Hui]

and it works for negative values as well

Forgot to delete that line

 

[TessaES]

If you only want to find the total, instead of also deleting it in the original data you could use you could use SUM(A1:Z1)/2 (assuming Z is the last column that has data). If all values are positive, you could also use MAX(A1:Z1)

 

[bernie3]

Hi Hui and TessaES, Thanks for the reply and help