• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help please

bernie3

New Member
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

Excel Ninja
Staff member
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

Excel Ninja
Staff member
and it works for negative values as well

Forgot to delete that line
 

TessaES

New Member
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)
 
Top