Join BKN

Join BKN
Join BKN

Bookkeeping Hall of Fame

Join BKN

Post Info TOPIC: How to set new 20% formula in Excel


Veteran Member

Status: Offline
Posts: 39
Date:
How to set new 20% formula in Excel


Good afternoon,

Could anyone tell me how to change the VAT rate in Excel please?

The package i use already has all formulae input so i'm stuck when it comes to changing any confuse

I input the cost of a purchase in one column, the Net in the second column and this automatically inserts the VAT into the third column so obviously at the moment it is inputting VAT at the 17.5% rate.
If anyone can help me with the new formula i would be grateful.

Thank you in advance  smile.gif

__________________


Member

Status: Offline
Posts: 6
Date:

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

__________________



Veteran Member

Status: Offline
Posts: 31
Date:

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

__________________
www.rsbookkeeping.co.uk www.twitter.com/RSBookkeeping


Veteran Member

Status: Offline
Posts: 39
Date:


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 smile.gif

__________________


Veteran Member

Status: Offline
Posts: 39
Date:

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

__________________


Forum Moderator & Expert

Status: Online
Posts: 8943
Date:

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.




__________________

Responses are not meant as a substitute for professional advice. Answers are intended as outline only and in all situations the advice of a qualified professional should be sought before acting on any response given.

Zoe


Senior Member

Status: Offline
Posts: 212
Date:

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

__________________

Seahorse (UK) Limited - Support for Bookkeepers and Accountants
www.seahorseuk.co.uk
01268 417631
Starting a Bookkeeping Business



Veteran Member

Status: Offline
Posts: 39
Date:

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.

__________________


Forum Moderator & Expert

Status: Online
Posts: 8943
Date:

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.

__________________

Responses are not meant as a substitute for professional advice. Answers are intended as outline only and in all situations the advice of a qualified professional should be sought before acting on any response given.



Veteran Member

Status: Offline
Posts: 39
Date:

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

Thank you, have a good day.

__________________


Member

Status: Offline
Posts: 6
Date:

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

Status: Offline
Posts: 896
Date:

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!


__________________

 

Regards,
Nick

Website: www.gbmaccounts.co.uk
Twitter

Factsheet | Starting a Business

 



Veteran Member

Status: Offline
Posts: 26
Date:

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!

__________________

Howtobookkeep.com is your one-stop-shop to find out how to bookkeep. Check out our blog or ask us for more info!



Senior Member

Status: Offline
Posts: 108
Date:

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

__________________

Ian

Ian Brown FCA
Onion Reporting Software Ltd

www.onionrs.co.uk

Sage accounts in Excel to go. No set-up necessary.



Forum Moderator & Expert

Status: Online
Posts: 8943
Date:

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.

__________________

Responses are not meant as a substitute for professional advice. Answers are intended as outline only and in all situations the advice of a qualified professional should be sought before acting on any response given.



Expert

Status: Offline
Posts: 1619
Date:

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.

__________________

Never buy black socks from a normal shop. They shaft you every time.

http://www.smbps.co.uk/



Senior Member

Status: Offline
Posts: 108
Date:

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



__________________

Ian

Ian Brown FCA
Onion Reporting Software Ltd

www.onionrs.co.uk

Sage accounts in Excel to go. No set-up necessary.

Page 1 of 1  sorted by
 
Quick Reply

Please log in to post quick replies.

Tweet this page Post to Digg Post to Del.icio.us
Members Login
Username 
 
Password 
    Remember Me  

BKN Special Offers

 

Resources

Careers & Qualifications  Start Up Essentials  Marketing Centre   Software Reviews
What is a book-keeper  Register as Self-Employed Networking  Software Blog
 Home Study Courses  Money Laundering Local Accountants  
 Qualifications/ Associations  Data Protection Direct Marketing  
 Franchises  Registering as an agent  Telemarketing  
 College / Night School  PI Insurance  Directories  
 Recruitment  Stationery  Google  
     Twitter  
     Volunteer Work  

 

 

 

 

 
 

 
   

 

Copy Right Bookcert Limited