Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Problem with Excel

Options
  • 26-10-2013 7:57am
    #1
    hexosan
    Registered Users Posts: 1,736 ✭✭✭


    Hopefully I have this is the correct place if not mods feel free to move it.

    This is my excel problem

    If I have 3 columns A, B & C with lines from 1-100 and I want to deduct the value in B from A to give me a plus or minus answer in C, I know I use the formula

    =sum(b1-a1)

    My problem is do I have to type that for each line from 1-100 or is there a was of selecting the entire column and replicating it.

    Thanks for any help.

    PS I had a workbook that used to do all this for me but it got damaged and I lost all the info. When the calculation was made it showed the answer in either black for positive and red for negative I can't seem to get this feature back either.


Comments

  • Options
    #2
    brightspark
    Registered Users Posts: 2,637 ✭✭✭brightspark


    http://spreadsheets.about.com/od/excelformulas/ss/2010-12-25-excel-2010-basic-tutorial-pt1_9.htm


  • Options
    #3
    ciaran76
    Registered Users Posts: 4,694 ✭✭✭ciaran76


    Copy cell C1 and paste down the 99 rows should work.


  • Options
    #4
    Trampas
    Registered Users Posts: 7,680 ✭✭✭Trampas


    Just drag down c1 to as far as you require


  • Options
    #5
    dixiefly
    Registered Users Posts: 2,949 ✭✭✭dixiefly


    Another tip, if you hover the mouse over the bottom right hand corner of the cell C1 the cursor will change to a +. Another tap will copy the formula all the way down associated with whatever batch of data you have.


  • Options
    #6
    hexosan
    Registered Users Posts: 1,736 ✭✭✭hexosan


    Thanks for all the answers so far ill give the copy a try. Can anyone answer my other question about the black/red answer.


  • Advertisement
  • Options
    #7
    dmc17
    Registered Users Posts: 3,746 ✭✭✭dmc17


    Conditional Formatting for black or red. Highlight all the cells you want it to work on and click Conditional Formatting on the home tab. Then click 'highlight cell rules' if you want to just highlight to cells. If you want to change the text colour I think you have to go down to 'new rule' and then 'format only cells that contain'. Then you set it for 'cell value' 'greater than' 0 and click 'format' to set text colour. Repeat for less than 0.


  • Options
    #8
    brightspark
    Registered Users Posts: 2,637 ✭✭✭brightspark


    It's also possible to do it within the format numbers options.

    http://spreadsheets.about.com/od/excelformatting/qt/090830-excel-negative-number-format.htm

    You can also specify the number of decimal places etc.


    Unlike Dmc17's suggestion it only has the option of red for negative and black for positive numbers.


Advertisement