IF formula

[piopakk]

Hi all

I have a long list of rows (500.000 rows), based on two colums: 1) product codes 2) price

Each line of product code (contains numbers and letters) has a price.

I need to create a fomula in a third column (named 'new price') that creates a new price of all product codes that begins with the letters 'TELMO'. Those product codes that contain 'TELMO', shall have a new price: Price-100. After that, the same formula must also search for all product codes that begins with 'Basis', and create a new price to: price + 20.

Does that make sense?

Example:
Product code Price New price
Telmo10B 219 219-100
Basis60G 0 0+20

I now it's something with a If formula, probalby two if's in the same formula.

Thanks.

[AlanY]

try this

=IF(LEFT(A2,5)="Telmo",B2&"-100",IF(LEFT(A2,5)="Basis",B2&"+20",""))

[piopakk]

I tried this formula, but something does not work. Please look at this attached screenshot of the worksheet. I need the new price in the AV column.
Udklip.JPG

[ChemistB]

A spreadsheet upload (Go Advanced>Manage Attachments) is better than a picture.

Assuming row 2
adjusting Alan's formula

=IF(LEFT(AC2,5)="Telmo",AD2-100,IF(LEFT(AC2,5)="Basis",AD2+20,AD2))

[piopakk]

That worked, thanks guys! Appreciated.