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

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

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!