Groups

    Excel Help with %'s plzzz

    Hey, im lookin for an answer to the below question if anyone can help?

    basically i want to add on 40% to all prices below £250 and then i want to add on 30% to all amounts between £251 and £700 and i want to add on 20% above £701.

    this is an example of the numbers i have.....

    i would want the extra to be added to the amount that is currently there, e.g £34.00 would change to £47.60

    £34.00 £29.00
    £42.00 £34.00
    £73.00 £44.00
    £96.00 £59.00
    £150.00 £80.00
    £226.00 £125.00
    £399.00 £205.00
    £570.00 £298.00
    £781.00 £400.00
    £961.00 £487.00
    £1,130.00 £572.00
    £1,343.00 £681.00
    £1,512.00 £760.00
    £1,684.00 £853.00
    £1,900.00 £955.00
    £2,236.00 £1,133.00
    £2,618.00 £1,323.00
    £2,790.00 £1,408.00
    £3,006.00 £1,518.00
    £3,730.00 £1,883.00

    thanks

    14 Comments

    If you value is in cell A1, then type this into B1 (and drag down)


    =IF(A1=251,A1

    Banned

    So say you have these in a big list and sorted them by value and you are starting at box A1 and it contains £29.00. In box B1 type =A1*1.4.

    That will give you the price for £29 + 40%.

    Then drag the bottom right corner of box B1 down to the high value under £250 (£205.00). you should now have all prices under £250 with an extra 40%.

    For 30% use 1.3 and for 20% use 1.2.

    Original Poster

    Ambdon;6451901

    If you value is in cell A1, then type this into B1 (and drag … If you value is in cell A1, then type this into B1 (and drag down)=IF(A1=251,A1

    Don't mean to be funny but the time its taken you to write all the numbers down, you could have worked it out yourself!!!!

    Original Poster

    Mrs O;6451919

    Don't mean to be funny but the time its taken you to write all the … Don't mean to be funny but the time its taken you to write all the numbers down, you could have worked it out yourself!!!!



    i have about 10 pages of values each with about 100 numbers in lol :whistling:

    max401;6451908

    excellent, only problem is i have values in cells C4 to K23??



    Where do you want the results? if you've got a block of numbers - do you want them underneath? or in a block next to it??

    All you have to do is paste the formula in teh cell where you want the answer, and then replace A1 with the cell where you value is... (note: a1 will have probably changed when you pasted the formula into a different cell...)

    let me know how u get on - if not, upload the file and i'll have a look for you!

    Amb

    Original Poster

    Ambdon;6451937

    Where do you want the results? if you've got a block of numbers - do you … Where do you want the results? if you've got a block of numbers - do you want them underneath? or in a block next to it??



    it would be best if i could add it on to the current values so they just change

    that would create a circular formula and you'll end up with millions of annoying pop ups and arrows all over the screen! lol

    What are you trying to do? if you want it automatic - you need to have a range of cells which are teh same size/dimensions as your range (e.g. next to it or on a different sheet) and then whatever changes you make to ur original range, the new range will automatically be updated!

    e.g.
    (original range) _____(new range with formulas linked to original cells)
    | 100 | 200 |________| 140 | 280 |
    | 400 | 500 |________| 460 | 590 |

    Original Poster

    ummm ok... how about this then...

    my current numbers are now in B4-F23. what if i have the Total value of these cells with the percentage added on in cells H4-L23

    would this help?

    yup, then all you have to do is type this in H4:
    =IF(B4=251,B4
    and then drag it across to L4 and then down to L23!

    That should give you a duplicate table of ur original B4-F23 numbers but with your % increases!

    Is that what you wanted?

    Original Poster

    Ambdon;6452064

    yup, then all you have to do is type this in H4:=IF(B4=251,B4and then … yup, then all you have to do is type this in H4:=IF(B4=251,B4and then drag it across to L4 and then down to L23!That should give you a duplicate table of ur original B4-F23 numbers but with your % increases!Is that what you wanted?



    AWESOME AWESOME AWESOME!!!! :thumbsup:

    thankyou, what would i do without hukd?!? :whistling:

    rep left for you.. :thumbsup:

    lol! welcome to the fun world of excel! Just wait till you try pivot tables! haha! :thumbsup:

    Original Poster

    Ambdon;6452131

    lol! welcome to the fun world of excel! Just wait till you try pivot … lol! welcome to the fun world of excel! Just wait till you try pivot tables! haha! :thumbsup:



    wtf is a pivot table? hahaha :w00t:

    i've used em for years and still don't really know
    it's a way of looking at/summarising data in a table.... if you end up working in accounts - you'll become an expert!
    Post a comment
    Avatar
    @
      Text
      Top Discussions
      1. Cat Chat73707
      2. Sad news about Barcelona23100
      3. How dangerous is Donald trump?2297
      4. Any PS4 News Here29213614

      See more discussions