Excel Help with %'s plzzz - HotUKDeals
Get the HUKD app free at Google Play

#### Search Error

An error occurred when searching, please try again!

# Excel Help with %'s plzzz

7y, 3w agoPosted 7 years, 3 weeks ago
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
7y, 3w agoPosted 7 years, 3 weeks ago
Options

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

=IF(A1< =250,A1*1.4,IF(AND(A1>=251,A1<=700),A1*1.3,A1*1.2))
banned#2
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.
#3
Ambdon
If you value is in cell A1, then type this into B1 (and drag down)

=IF(A1< =250,A1*1.4,IF(AND(A1>=251,A1<=700),A1*1.3,A1*1.2))

excellent, only problem is i have values in cells C4 to K23??
1 Like #4
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!!!!
#5
Mrs O
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:
#6
max401
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
#7
Ambdon
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 :)
#8
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 |
#9

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?
1 Like #10
yup, then all you have to do is type this in H4:
=IF(B4< =250,B4*1.4,IF(AND(B4>=251,B4<=700),B4*1.3, B4*1.2))

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?
#11
Ambdon
yup, then all you have to do is type this in H4:
=IF(B4< =250,B4*1.4,IF(AND(B4>=251,B4<=700),B4*1.3, B4*1.2))

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?

AWESOME AWESOME AWESOME!!!! :thumbsup:

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

rep left for you.. :thumbsup:
#12
lol! welcome to the fun world of excel! :) Just wait till you try pivot tables! haha! :thumbsup:
#13
Ambdon
lol! welcome to the fun world of excel! :) Just wait till you try pivot tables! haha! :thumbsup:

wtf is a pivot table? hahaha :w00t:
#14
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! :)