Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2013
    Posts
    1

    Unanswered: Help wit formula

    Hello people....I need to explain what I'm doing so you can see where is my problem.

    In my first column (E) are some numbers in 140 rows and at the end is SUM of all numbers in that column(E140). In the next column is formula like this =E5/E140*100 (so I can get percentage of every number to SUM of all numbers in E column) and in the next row of that column is =E6/E140*100 and so on until =E139/E140*100 where E140 is sum of all numbers in first column.

    Usually my charts are around 140 rows so if I have couple more rows I just write couple more formulas or delete couple rows if I have less rows and there is no big problem but now I have chart with 800 rows. I just inserted aprox. 700 rows in my chart and formula in my first 140 rows is ok ( =E5/E840*100 all the way to =E139/840*100) but rest of cells are empty and when I try to replicate formula (with "+" in lower right corner of cell, like I do when I'm doing SUM or subtracktion and everything is ok) formula in next cell is wrong and it says =E140/E841*100 and next field say =E141/E842*100 and because E841, E842 and so on and on are empty everything in that column is wrong and has no value.

    what do I need to add to formula or do I need some other formula that second part (E840) of it is constant through out whole column and first part (E5 to E839) will change so I dont need to write or correct whole bloody thing, it will take two days to do it.

    Really hope someone understands what is my problem and helps me with it....thanx

  2. #2
    Join Date
    May 2013
    Posts
    8
    Can you turn your data into a table, then as you add more data/rows your chart should update automatically.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    To expand slightly on rosuco's answer above:

    In Excel 2007 and onwards:
    Highlight your data set and on the "Home" tab of the Ribbon click "Format as Table".
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •