How to set new 20% formula in Excel - The Book-keepers Forum (UK)

blondie · Veteran Member

How to set new 20% formula in Excel

Lynwood · Member

Could you copy and paste me the formula to enquiries@lynwoodbookkeepers.co.uk and i will try and change it for you
Thanks Neil

RSBookkeeping · Veteran Member

Sounds like the formula may say something like =NETCELL*17.5%
Try changing it to =NETCELL*20%

blondie · Veteran Member

Thank you for both replies. I have only just noticed them so i will try changing the formula as 'rs' said. If this doesn't work then i will email you Neil.

Thank you both

blondie · Veteran Member

Thank you so much.

I used the '*20%' formula and it has worked a treat. Only problem is the spreadsheet has 502 rows so it will take me a while to change the whole thing! But at least i'm on the new VAT rate now.

Thanks again for your help biggrin

-- Edited by blondie on Monday 31st of January 2011 11:36:01 AM

Shamus · Forum Moderator & Expert

Hi,

if you are in the unfortunate position that you have actually got to go in and change every cell (rather than just dragging one cell over the rest of original formulae) then rather than change it to the *.2 change every cell to a pointer.

Set up your VAT rate in say cell B2 and then get every cell to refer to $b$2. Note that prefixing with the $ sign means that when you copy / replicate the cell the pointer remains as a constant rather than Excel incrementing the value to what it thinks that you want.

If you are using this formula to extract the VAT from a figure then best you set up two pointers.

17.5% was *7/47.

20% is *1/6

So in either case if every cell was *$b$1/$b$2 then at each change of VAT rate all that you need to do to update your spreadsheet is to alter cells B1 and B2.

However, gets a bit more complex when the VAT change is part way through a company year in which instance you are likely to end up with three spreadsheets. One at each VAT rate and a totals sheet which accumulates the values in the cells from the other two sheets. (entries only appearing at one rate so existing only in one of the two sheets).

Hope that this helps rather than confuses matters.

All the best,

Shaun.

Zoe · Senior Member

Try holding the bottom right corner of the cell that your new formula is in and drag it down to the rows you want to change. This will copy the formula into those cells instead of retyping each one. Save your work before you start though in case something goes wrong.

Kind regards

Zöe

blondie · Veteran Member

Oh wow..thank you so much for those tips! This is going to make the job a whole lot easier..and quicker!
I will give these a go.

Thanks again.

Shamus · Forum Moderator & Expert

If you are using Excel a lot there is one book that I can't harp on enough about.

Its Excel 2007 Data Analysis and Business Modelling. ISBN-13: 978-0735623965

Its also good for office 2010 but not a lot of use for pre 2007 versions of Excel.

You really will be surprised at some of the things that Excel can do.

Good luck and have fun.

If you have any other Excel queries don't hesitate to ask.

Shaun.

blondie · Veteran Member

Thank you for that advice. I think i may have to purchase that!

Thank you, have a good day.

Lynwood · Member

A very good friend of mine Sean Blessit is an excel guru and a really nice guy too , if you say big neil gave you his details he is more than happy to give you a few pointers in excel

sean@astradyne.com

gbm · Guru

Hi Blondie,

Another way of doing this - if, for instance, the forumla wasn't in a particular column - would be to use 'replace'. Click on replace, put 17.5 in the 'find what' box and 20 in the 'replace with' box. I find this one quite useful for changing stuff quickly. It works the same in Word too.

One of the great (I think?) things about Excel is that there can be more than one way to do something, and you find that you struggle with one way of doing something for years and then find another way. I used copy and paste for years but never used format painter, when I learnt about that, thought it was fantastic!

Howtobookkeep · Veteran Member

I teach one-on-one excel online classes, just head to my website and click "learn excel" or message me on here or on my website. Glad you found the answer you needed!

bro0010 · Senior Member

Blondie,

Shamus wrote:
Set up your VAT rate in say cell B2 and then get every cell to refer to $b$2.

If you select cell B2 and type VATrate in the Name Box at the left of the Formula bar you can then use VATrate instead of $B$2 in all your formulae. It's the same thing but I find it makes the formulae more readable.

Regards

-- Edited by bro0010 on Friday 22nd of August 2014 11:01:43 AM

Shamus · Forum Moderator & Expert

Morning Ian,

I've never really been a fan of naming fields and ranges generally as I've generally needed to keep references flexible.

Excel is one of those bits of software where there's always a dozen right ways to do something and I prefer the typing in a reference that I can f4 as many times as I need for fast population.

In the case in question of course as you state naming works perfectly and there is absolutely nothing wrong with going down that path. I personal just prefer flexible referencing.

Hope that your having a good day Ian,

talk soon,

Shaun.

p.s. For anyone who doesn't use f4 when locking cell references with dollar signs f4 pages through the locks (lock both, lock row, lock column, no lock) until you hit the one that you are looking for. After a while doing that is much faster than typing dollar signs into formulae.

Peasie · Expert

I knew about the F4 thing to lock it onto a particular cell - I never knew hitting the F4 again would scroll through the other locks - although 93% of the time I use the $ to lock anything it is locking on to a particular cell.

bro0010 · Senior Member

Shaun,

I think I'm probably in agreement with you about range naming. Single cell references with double $ locking is one of my "exceptions" to the general rule. biggrin

(I did note the other day though that naming is almost a "must" if a cell is one of a number used as a scenario input).

Have a good weekend.

Regards