Excel Help with %'s plzzz - HotUKDeals
We use cookie files to improve site functionality and personalisation. By continuing to use HotUKDeals, you accept our cookie and privacy policy.
Get the HotUKDeals app free at Google Play

Search Error

An error occurred when searching, please try again!

Login / Sign UpSubmit

Excel Help with %'s plzzz

£0.00 @
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… Read More
max401 Avatar
7y, 8m agoPosted 7 years, 8 months 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
max401 Avatar
7y, 8m agoPosted 7 years, 8 months ago
Options

All Comments

(14) Jump to unreadPost a comment
Comments/page:
#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
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?
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! :)

Post a Comment

You don't need an account to leave a comment. Just enter your email address. We'll keep it private.

...OR log in with your social account

...OR comment using your social account

Thanks for your comment! Keep it up!
We just need to have a quick look and it will be live soon.
The community is happy to hear your opinion! Keep contributing!