Letters to represent numbers
 

I have a spreadsheet with three basic columns,

Town Category Cost
Sheffield A £
Bath B £
Manchester A £
Newquay C £
Stamford D £


the category is based on size of the town and are either A, B, C or D.
each category has an equal set cost (e.g A= £100, B= £80, C= £60, D= £40)
I need to know if there is a formula to automatically fill in the cost when
I enter the category in column B? hope this makes sense!

Letters to represent numbers
 

Try this,

Build a simple table of category/cost like this


Col D Col E
A 100
B 200
C 300


Then in C2 type this and drag down. Note that my table is in D1-E10 so alter
to suit.
=VLOOKUP(B1,D$1:E$10,2,FALSE)

Mike


"AlexG" wrote:

I have a spreadsheet with three basic columns,

Town Category Cost
Sheffield A £
Bath B £
Manchester A £
Newquay C £
Stamford D £


the category is based on size of the town and are either A, B, C or D.
each category has an equal set cost (e.g A= £100, B= £80, C= £60, D= £40)
I need to know if there is a formula to automatically fill in the cost when
I enter the category in column B? hope this makes sense!

Letters to represent numbers
 

Alternatively, rather than build a second table, just enter this
formula in C2 (under Cost), format as currency, and copy down:

=IF(B2="","",(70-CODE(B2))*20)

Hope this helps.

Pete


On Jul 26, 12:12 pm, Mike H wrote:
Try this,

Build a simple table of category/cost like this

Col D Col E
A 100
B 200
C 300

Then in C2 type this and drag down. Note that my table is in D1-E10 so alter
to suit.
=VLOOKUP(B1,D$1:E$10,2,FALSE)

Mike



"AlexG" wrote:
I have a spreadsheet with three basic columns,

Town Category Cost
Sheffield A £
Bath B £
Manchester A £
Newquay C £
Stamford D £

the category is based on size of the town and are either A, B, C or D.
each category has an equal set cost (e.g A= £100, B= £80, C= £60, D= £40)
I need to know if there is a formula to automatically fill in the cost when
I enter the category in column B? hope this makes sense!- Hide quoted text -

- Show quoted text -

Letters to represent numbers
 

Another way...........

=LOOKUP(B2,{"A","B","C","D"},{100,80,60,40})

Vaya con Dios,
Chuck, CABGx3



"AlexG" wrote:

I have a spreadsheet with three basic columns,

Town Category Cost
Sheffield A £
Bath B £
Manchester A £
Newquay C £
Stamford D £


the category is based on size of the town and are either A, B, C or D.
each category has an equal set cost (e.g A= £100, B= £80, C= £60, D= £40)
I need to know if there is a formula to automatically fill in the cost when
I enter the category in column B? hope this makes sense!