Groups

    Excel help required please

    Banned
    Not sure if this is possible but I need to add a set percentage onto a column of prices in excel, is there any easy way of doing it?

    To explain slightly more the sheet is set out

    PRODUCT COLOUR RRP TRADE PRICE

    I need to add a set % to the whole trade price column, there's around 500 products so doing it individually takes hours!

    17 Comments

    can you add the formula
    [figure] divide by 100 multiply by [percentage amount]

    In a blank cell put the formula = (trade price cell) + whatever percentage you want
    Then hover over the cell. In the bottom right hand corner, there is a black square. Put your mouse over this and your cursor turns into a black cross. Then click and drag and the formula will be repeated in all the other cells below. Hope this makes sense???

    Carley

    can you add the formula[figure] divide by 100 multiply by [percentage … can you add the formula[figure] divide by 100 multiply by [percentage amount]


    marktuck

    In a blank cell put the formula = (trade price cell) + whatever … In a blank cell put the formula = (trade price cell) + whatever percentage you wantThen hover over the cell. In the bottom right hand corner, there is a black square. Put your mouse over this and your cursor turns into a black cross. Then click and drag and the formula will be repeated in all the other cells below. Hope this makes sense???



    I don't feel very well................

    Original Poster Banned

    marktuck

    In a blank cell put the formula = (trade price cell) + whatever … In a blank cell put the formula = (trade price cell) + whatever percentage you wantThen hover over the cell. In the bottom right hand corner, there is a black square. Put your mouse over this and your cursor turns into a black cross. Then click and drag and the formula will be repeated in all the other cells below. Hope this makes sense???



    that didn't work - although i probably did something wrong - ill point out here im useless with excel and need a step by step guide lol - thanks

    This screen shot may make it clearer?

    http://www.btinternet.com/~marktuck/HUKD/Excel1.JPG

    The highlighted part is the formula, the part circled is the box to drag

    Original Poster Banned

    nope lol
    i did
    =J2+40% then enter and it put 269.00% into the box lol

    http://i141.photobucket.com/albums/r60/dvds2000/hukd/ex.jpg

    Original Poster Banned

    sorry pathetic forum software resizes everything so small you can#'t see it

    Right click the cell with 269% in and format cell to general.

    Original Poster Banned

    nope
    what its doing is

    I'm typing =J2+40% when I'm pressing enter its adding .4 onto the figure

    Why not just do =J2*1.4 then?

    or long windidly

    =J2+J2*40%

    Original Poster Banned

    because I've just worked that out - told you i knew nothing lol

    still, I need to do it for the whole of the column, do i have to do each cell individually?

    No, as in the previous post you can grab the square in the right hand bottom corner and drag down the column or just double left click it and it will auto fill the column. Autofill will only continue as long as there is data in the corresponding cell in column J. If say J10 is empty then it will stop autofilling at J9.

    Or, once you have created the formula you need in one cell, highlight it with a left button click, then click the right mouse button and select 'copy' then highlight all the cells you want to add the formula to and then select 'paste'

    wots excel?

    only kiddin

    Original Poster Banned

    ok getting there
    if a do the forumla in a new colum and drag it down it does it right, but if I try to do it over the original values it goes mad lol.

    I have found, if I do the new column then copy to notepad, undo changes, then paste from np back to the column that works, so that will do - certainly easier than doing 500+ products manually every week

    Many thanks to everyone who helped

    You can't do it over the original values because they are part of the formula, you will then end up with circualar formula that never end. (if I am understanding you correctly)

    If you are doing it every week you could write a simple macro (it is very easy) that will do it all at the press of a button.

    get the formula right in the first cell then select the following cells and use the autofill function.
    Post a comment
    Avatar
    @
      Text
      Top Discussions
      1. Show us your Photos 2017. September's Theme - 'Earth, Wind & Fire'31447
      2. The obvious next step become Ken then become Barbie87
      3. I want to talk about the WEATHER no politics no religion19047097
      4. Just heard this...2 ★★★★★★★★★★★★★★ congrats to all on 392k ★★★★★★★★★★★★★★7767204

      See more discussions