Spread sheet help - IF formula???

[valve90210]

Hi All

This is probably a really simple thing to do but not being someone who uses excel or open office Calc un actual fact, I'm not sure quite how to acheive what I'm hoping to do.

I am putting together a simple spreadsheet with the prices of a few items, some of which need to have VAT added to the list price, some don't.

I'd like to be able to enter the list price in one column (D) and then in the next column (E) enter 'y' or 'n'. In the column to the right (F) I'd like to be able to enter an IF formula so the spreadsheet will look at the E column, if I have entered y, it will then multiply the D colum value by 1.15 and enter the price including VAt, or if I have entered n, it'll simple put in the same value as the D column,

I thought the formula would be something along the lines of:

=IF(E3=yes,=SUM(D3*1.15),D3)

but it just gives me an error and I'm not sure why.

can anyone help me out with this?

Any help very much appreciated.

Thanks

Valve

 

[Ricki]

Tried and works

=IF(E1="yes",D1*1.15,D1)

Going by Excel logic, you need the quotes around the yes so it knows it is text and not just some weird number. Also the =SUM() is used to calculate the sum of a range of cells, so for example SUM(E1:E4) would be the same as E1+E2+E3+E4. There might be a use for the extra equals within the true part of the if statement but I have never used or come across it. I'm not sure if you can use a wild card as part of the if statement, by that I mean sometimes you may use y, sometimes yes and sometime mistype, so by using a wild card, such as an asterisk in the if statement you will always get it right, such as =IF(E1="y*",...)

 

[namnoc]

=IF(E3="yes",D3*1.15,D3)

 

[Antar Bolaeisk]

For future reference valve90210, when nesting different functions, ie placing one function within another, you can dispense with any subsequent equals sign. The first equals sign is required simply to let excel know that what is contained in the cell is a function.

Also, the =Sum([insert stuff here]) command sums up all the values of the cells listed within the brackets. While SUM(D3*1.15) will work, you don't really need it simply because there's nothing being added there, you just have the multiplication of the cell. As shown above, this can easily be achieved by entering the formula exactly as required.

Finally, the last reason your formula would have returned an error is because of the way you phrased the logical test. When making a check against a word you need to specify the word withing quotation marks indicating to excel that it is dealing with a string.

Edit: Bah, Ricki has it all there already, I need to read threads better.

 

[valve90210]

Thanks for your help guys, will try it this evening.

I did try it without the =Sum part but when it didn't work, I gave it a try...lol Must just have been the lack of quotation marks.

Thanks again for all the help.

Valve

 

[valve90210]

Tried that formula and it wasn't working initially, so just for info, Open Office calc requires semi colons between the sections of the formula rather than commas,

Valve

 

[Confused]

I would also note that it would be best to place your VAT rate into another cell, rather than hard-coding it in the formula.

Especially as it is changing back to 17.5% on 1st January.

 

[Alpha]

I would also note that it would be best to place your VAT rate into another cell, rather than hard-coding it in the formula.

Especially as it is changing back to 17.5% on 1st January.

Agree, just put in on your spreadsheet once and give it an absolute reference in your formulae. eg. $a$1

Then when you copy your formula, the vat rate will always be referred to cell A1, or wherever you put it.

So your formula would be: =IF(E3="yes",D3*$a$1,D3)

 

[daniel_owen_uk]

No real need to have a seperate place for the VAT rate, if you are just using one column (F:F) then you can simply do a find and replace on 1.15 to 1.175 when the rate changes.