My knowledge of Excel is a bit limited - I have set up a simple spreadsheet and all I need is a cell which has a formula which says if cell B1 is larger than zero multiple B1 by 17.5% but I cant get the formula right - can some kind sole help me?

Thank you

## Replies

## Please login or register to join the discussion.

Here we go...

+IF(B1>0,+B1*0.175,"")

Hope that helps.

Gary

www.ghornby.co.uk

Might be better to use

=IF(B1>0,B1*0.175,0)

to ensure both results are numeric, which might be relevant to something else down the line. This assumes you want to return zero where B1 is less than 0. If you actually want to return the negative value in B1 change

,0)to,B1)Call me Little Miss Fusspot

I can't bear things not to add up correctly so I always use the rounding function as well to cut things to two decimal places.

=ROUND(IF(B1>0,B1*.175,0),2)

Sorry, just can't bear ending up with things like 23.75698256512 and not balancing to the penny.

Thank you all very much - much appreciated.

I prefer ...

MAX(0,ROUND(B1*0.175,2))

But it's only personal preference and no better than any of the other suggestions.

So many ways to skin a cat

=B1*0.175*(B1>0)

(and round the whole thing, of course)

With kind regards

Clint Westwood

Spreadsheet rule 1

Don't put hard numbers into formulae!!

I assume that your 17.5% is the VAT rate and when this changes in January 2011 you will need to refomulate your calculation, so:

Put the 2010 rate of 17.5% in one cell and the 2011 rate of 20% in another cell and link the calculations to the appropriate VAT rate cell.

You could include a link to the date with the VAT rate cell being slected depending on whether the invoice date is before or after the change in rate - but that is getting complex

Stgreg - no I love it. Please, tell us how to do the date linked rate thingy. It's right up my street. I love geeky stuff. Spill....

If Statements

if you just want vat element then

=If(B1>0,b1*0.175,b1)

this next one will add vat if b1 is greater than zero so your answer will be b1 + vat

=If(B1>0,b1*1.175,b1)

or if b1 is inc vat and you need to know the vat then

=If(B1>0,b1-(b1/1.175),b1)

or if b1 is inc vat and you need to know net amount then

=If(B1>0,(b1/1.175),b1)

More on excel spreadsheets

Excel spreadsheet Suggestion

Set up:

Cell A1- A4: “Date VAT rate changes”. Merge all four cells and wrap text.

Cell B1 3/1/2011

Cell B2 31/12/2019

Cell B3 31/12/2020

Cell B4 31/12/2021

Cell C1: “VAT rate”

Cell D1 – D4: Format to percentage 2 decimal places.

D1 = 17.5%

D2 = 20.0%

D3 = 15.0%

D4 = 25.0%

Cell E1 “Decimal Places”

Cell F1 2 (or however many or few decimal places you want!)

Insert data in rows 7 downwards

Col A date

Col B Goods value

Col C formula to calculate the VAT.

Cell C7 reads

=IF(B7=0,0,ROUND(B7*(1+IF(A7<$B$1+1,$D$1,IF(A7<$B$2+1,$D$2,IF(A7<$B$3+1,$D$3,$D$4)))),F$1))

Copy this formual down as far as needed.

Have fun!!

PDF

ROUND???

What's with the use of the Round function??

Not only is using the round function completely unnecessary here, it would also likely cause problems if this simple calculation was part of a larger spreadsheet.

If you want to see only 2 decimal places then simply use Excel's number format function to format to 2 decimal places.

Rounding can cause problems if this calcualtion is used as part of a bigger picture - say a a balance sheet when both sides don't balance exactly (invariably a difference of +1 or -1). Rounding errors like this example are far worse, especially when you can retain full number integrity by simply formatting to 2 decimal places....

Roundings in spreadsheets

But formatting does not affect the info the spreadsheet holds. It will hold data to umpteen DP but only show what you have formatted to. If you do a summation formula on a column of figures that have been formatted without rounding, you will often get an apparent mistake.

I agree that when working back a pre-determined total special care is needed with roundings. That is a separate issue.

PDF

CAREFUL WITH ROUNDING

But formatting does not affect the info the spreadsheet holds.

Clearly - which is my whole point about NOT rounding.

It will hold data to umpteen DP but only show what you have formatted to. If you do a summation formula on a column of figures that have been formatted without rounding, you will often get an apparent mistake.'

Apparent' butmathematicallycorrect. With your logic you will invariably get more mistakes - i.e. 2.5% of something is not the same as 3%, yet with rounding both will be calculated as 3%. Then you get into the whole area of trying to get things to add up to 100%.Rounding has a place, you just need to know when to use it.

.

But since the VAT amount does have to be rounded individually for every invoice raised, I'd say that in this case you do need to round every calculation of VAT. The fact that several £1.0049's of VAT will add up to more than the same number of £1 VAT's does not mean you'll be declaring more VAT.

Problem with these formulas

The problem with all these formulas is they all need manual updating when the VAT rate changes.

Not only that, but variable rates might apply.

To get round this, you need a table somewhere in your workbook with two columns -

Rate_name - ie Standard, Exempt, Zero etc

Rate - 17.5%, 20% 0% etc

Then, define a name for the table

Insert a column for VAT RATe in to your spreadsheet and then use data validation to point at the source List >

="<your table name>"

This will populate a dropdown box in your worksheet that will allow the user to select the VAT rate for the transaction.

Then in your formula refer to the VAT Rate cell in the transaction row ie c6 or whatever

This way, when VAT rates change, you only have to update one value in one worksheet rather than tempt fate by updating hundreds of cells across several worksheets

BTW - this is a quick answer not a precise tutorial

Time stamped VAT rate using vlookup

If you have a data table with dates in column A and rates in column B and sorted by date then you can use vlookup to return the appropriate rate for a date as follows:

=vlookup([date being looked up], [lookup range], 2, True)

With the last parameter set to True, vlookup finds the next largest value that matches. So you need to put in the last date for which the VAT rate is applicable.

This approach is simpler and more flexible than the multiple if statement mentioned elsewhere.

Regards,

Jason Raikes

www.pendragonsystems.com

Getting the total right

Yes but these niggling rounding differences are a real nuisance. They sometimes make the result look wrong when it is right, and sometimes actually produce the wrong answer. They make it more difficult to distinguish between real errors, for example where you set up a formula wrong, and differences due solely to rounding. And they make you look inefficient.

For example, take a transaction where the VAT at 17.5% should be £1.214. One rounds to £1.21 and everyone is happy. Now imagine that you have three more identical transactions. The total VAT would be (£1.21 x 4 =) £4.84, but the spreadsheet would show (£1.214 x 4 =) £4.86. The odd 2p may not be material, but it makes you worry about the spreadsheet.

To deal with this, you can force Excel to calculate the figures it sees. You can force it to add up the four amounts of £1.21 and reach £4.84. You do this in Office 2007 by setting ‘Precision as displayed.’ Go to the Office Button, Excel Options, Advanced, Display options for this worksheet and then tick “Precision as displayed”. Warning – once you have set it you can’t unset it in that spreadsheet. (I can’t remember where precision as displayed is hidden in Excel 2003, but it has been available since the late 1990s at least).

And by the way isn’t there a VAT rule that you can always round fractions of a penny down in favour of the taxpayer, such that, if 17.5% = £1.218 the correct VAT is also £1.21?

-- Clive Tulloch

Sorry ...

... about the rubbish in the first half of my reply. I innocently copied and pasted from Word. I have asked the editors to remove it (apparently I cannot).

-- Clive Tulloch

All fixed

Sorry for the textual messiness there, Clive.

Our Drupal content management system has some temperamental difficulties handling text from Microsoft Office documents. If you want to paste copy on to the site from MS Word, please click the little W Clipboard icon on the "Body" menu bar when posting a comment.

Even this approach sometimes causes slight formatting issues. If you paste text in from MS Notepad, it comes out much better - so this is what I end up doing everytime I post something on the site.

We're examining some upgrade tweaks for AccountingWEB.co.uk - maybe this little glitch is something that we could fix to make things easier for everybody!