Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function that will look at letter in a column and return a number
I am making a spreadsheet of what's withheld from employee's paychecks (every
2 weeks) (e.g., health insurance premiums, Flex Spending, etc). A B C 1 John (F) 2 Mary (S) Column B lists what kind of coverage the employee has--"F" is Family, "S" is Single, "C" is Couple--each coverage has a different premium (price). I need a function for column C that will look at cell B1 and see that "F" means John subscribes to Family Coverage, and return (to column C) a particular value; look at cell B2 and see that "S" means Mary subscribes to Single coverage, and return a different value. Is there a function/formula that I can utilize to reach this goal? Please, if you're answering, explain in Lehman's terms. Thanks in advance! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function that will look at letter in a column and return a number
Try the below formula in B1..Hope you will have only these 3 entries in B1.
Lookup will look at the value in the 1st array which is f,s,c and return the corresponding value in the next array which is 30,10,20 respectively... =IF(B1="","",LOOKUP(B1,{"F","S","C"},{30,10,20})) If this post helps click Yes --------------- Jacob Skaria "locasciok1" wrote: I am making a spreadsheet of what's withheld from employee's paychecks (every 2 weeks) (e.g., health insurance premiums, Flex Spending, etc). A B C 1 John (F) 2 Mary (S) Column B lists what kind of coverage the employee has--"F" is Family, "S" is Single, "C" is Couple--each coverage has a different premium (price). I need a function for column C that will look at cell B1 and see that "F" means John subscribes to Family Coverage, and return (to column C) a particular value; look at cell B2 and see that "S" means Mary subscribes to Single coverage, and return a different value. Is there a function/formula that I can utilize to reach this goal? Please, if you're answering, explain in Lehman's terms. Thanks in advance! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function that will look at letter in a column and return a number
=IF(B1="","",LOOKUP(B1,{"F","S","C"},{30,10,20} ))
The lookup_vector *must* be sorted in ascending order. =IF(B1="","",LOOKUP(B1,{"C","F","S"},{20,30,10})) -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Try the below formula in B1..Hope you will have only these 3 entries in B1. Lookup will look at the value in the 1st array which is f,s,c and return the corresponding value in the next array which is 30,10,20 respectively... =IF(B1="","",LOOKUP(B1,{"F","S","C"},{30,10,20})) If this post helps click Yes --------------- Jacob Skaria "locasciok1" wrote: I am making a spreadsheet of what's withheld from employee's paychecks (every 2 weeks) (e.g., health insurance premiums, Flex Spending, etc). A B C 1 John (F) 2 Mary (S) Column B lists what kind of coverage the employee has--"F" is Family, "S" is Single, "C" is Couple--each coverage has a different premium (price). I need a function for column C that will look at cell B1 and see that "F" means John subscribes to Family Coverage, and return (to column C) a particular value; look at cell B2 and see that "S" means Mary subscribes to Single coverage, and return a different value. Is there a function/formula that I can utilize to reach this goal? Please, if you're answering, explain in Lehman's terms. Thanks in advance! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function that will look at letter in a column and return a number
Another way:
Using your chart, If you assign costs to each letter such as F= $1000 and so on, place F in cell f1, S in cell f2, and C in cell f3, etc. with the corresponding dollars in the g column, in cell c1 put: =IF(B1="","",VLOOKUP(B1,($F$1:$G$20),2,FALSE)) and then drag/copy it down. (I used $G$20 to give you up to 20 entries.) You can then adjust the dollar amounts in g if you need to. If you need more entries place the letters in column f, with the dollar amounts in column g. Again, the $g$20 will cover 20 entries. If you need more change the 20 to however many you need, and drag/copy the formula down again. Squeaky "locasciok1" wrote: I am making a spreadsheet of what's withheld from employee's paychecks (every 2 weeks) (e.g., health insurance premiums, Flex Spending, etc). A B C 1 John (F) 2 Mary (S) Column B lists what kind of coverage the employee has--"F" is Family, "S" is Single, "C" is Couple--each coverage has a different premium (price). I need a function for column C that will look at cell B1 and see that "F" means John subscribes to Family Coverage, and return (to column C) a particular value; look at cell B2 and see that "S" means Mary subscribes to Single coverage, and return a different value. Is there a function/formula that I can utilize to reach this goal? Please, if you're answering, explain in Lehman's terms. Thanks in advance! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function that will look at letter in a column and return a num
Oops. my mistake. Thanks Biff.
If this post helps click Yes --------------- Jacob Skaria "T. Valko" wrote: =IF(B1="","",LOOKUP(B1,{"F","S","C"},{30,10,20} )) The lookup_vector *must* be sorted in ascending order. =IF(B1="","",LOOKUP(B1,{"C","F","S"},{20,30,10})) -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Try the below formula in B1..Hope you will have only these 3 entries in B1. Lookup will look at the value in the 1st array which is f,s,c and return the corresponding value in the next array which is 30,10,20 respectively... =IF(B1="","",LOOKUP(B1,{"F","S","C"},{30,10,20})) If this post helps click Yes --------------- Jacob Skaria "locasciok1" wrote: I am making a spreadsheet of what's withheld from employee's paychecks (every 2 weeks) (e.g., health insurance premiums, Flex Spending, etc). A B C 1 John (F) 2 Mary (S) Column B lists what kind of coverage the employee has--"F" is Family, "S" is Single, "C" is Couple--each coverage has a different premium (price). I need a function for column C that will look at cell B1 and see that "F" means John subscribes to Family Coverage, and return (to column C) a particular value; look at cell B2 and see that "S" means Mary subscribes to Single coverage, and return a different value. Is there a function/formula that I can utilize to reach this goal? Please, if you're answering, explain in Lehman's terms. Thanks in advance! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Column() to return a letter instead of a number? | Excel Worksheet Functions | |||
How to replace column letter in refferences with a function using the old column letter? | Links and Linking in Excel | |||
How to replace column letter in refferences with a function using the old column letter? | Excel Worksheet Functions | |||
return the column reference number of a function result | Excel Discussion (Misc queries) | |||
column header changed from letter to number, how return to letter | Excel Discussion (Misc queries) |