excel - populating cells depending on value in other cells?

[LGJ]

Hi all,

Please can someone help me or point me in the right direction?

I have an excel sheet with example colum shown below:

Ring^Standard 300
Image^Colour 150
Game^New 250

I need to create a formula which populates the cell next to it with the cost value (300 = 3 pounds, 150 = 1.50 ...)

So soemthing like

if (cell = 300)
new_cell = 3.5
else if (cell = 250)
new_cell = 2.5
else if (cell = 150)
new_cell = 1.50

Problem is that I dont know how to do this in excel??

Any ideas?

Thanks
LGJ

 

[GlennUK]

At first I thought it was simple arithmetic until I saw ...

if (cell = 300)
new_cell = 3.5

So what rules govern the new value? Do you have a list?

Or did you do a typo, and actually meant that everything should be /100 of the original? If so, put =cellref/100 in your new cell and copy down.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[LGJ]

Hi Glenn,

Each product could have a value of say Name^Name Number and the number can be either 300, 250, 150, 500 for example.

The number relates to pence I want to get the cost of each product (in pounds) if it has a number attached to the description so I can then total up that column.

Is there any way to say if (cell like "%300") ??

Thanks
LGJ

 

[GlennUK]

If your Name^Name Number is all in the same column, I would split it so that the different parts end up in different columns. That way you can do analysis and arithmetic very very easily.

To split the column, select it and do Data/Text To Columns, and specify Space and ^ as separators.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.