Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i program alphabet letters to represent numerical values? | Excel Discussion (Misc queries) | |||
Sorting - cells containing numbers, numbers and letters | Excel Discussion (Misc queries) | |||
Numbers to Letters? | Excel Discussion (Misc queries) | |||
create self-generating numbers with letters and numbers | Excel Discussion (Misc queries) | |||
how do i represent numbers with letters | New Users to Excel |