If statement with multiple conditions
I am writing a complex formula and can use some help. I have two salary types in one column, and five levels of years of service in another column. I want Excel to print a different message for each of these 10 possibilities. The formula I am starting with is:
=IF(E5<=3&L5<5,"2 weeks") where column E shows the two salary types (below 3 or above 3) and column L shows years of service. I can get this much of the formula to work, but how can I get it to continue to print a different message for my other 8 options? Thanks. |
If statement with multiple conditions
One way would be to build a table with all the required results then
use =vlookup,Table,E5,L5) to extract the required info DavidP On Mon, 10 Nov 2003 08:36:04 -0800, "Kris" wrote: I am writing a complex formula and can use some help. I have two salary types in one column, and five levels of years of service in another column. I want Excel to print a different message for each of these 10 possibilities. The formula I am starting with is: =IF(E5<=3&L5<5,"2 weeks") where column E shows the two salary types (below 3 or above 3) and column L shows years of service. I can get this much of the formula to work, but how can I get it to continue to print a different message for my other 8 options? Thanks. |
If statement with multiple conditions
If you want to do this with code, try the following ( adjusting th columns as needed): I set this up as follows A2:A100 is named SalaryType B2:B100 contains years of service C2:C100 will contain the "Results" ie the text you want X2:X11 is named "Text" and contains the 10 possible "answers" such as "Two weeks" or "5 Days", "6 Days"...... There will be 2 salary types: 0 or 5 There will be 5 longevity categories: 1-5 By adding the two we get 1-10 The INDEX function in the last code line looks up the nth (1-10) valu in the "Text" range and returns the value to column C (which is offset 2 columns from the Salary col--adjust as needed) The VOID line allows for rows in your database that may be emplty Sub VacationTime() Dim oCell As Range Dim x As Integer Dim y As Integer For Each oCell In Range("SalaryType") 'Assign Salarytype category Select Case oCell.Value Case 1 To 3 x = 0 Case Is 3 x = 5 Case Else GoTo VOID End Select 'Assign longevity category Select Case oCell.Offset(0, 1).Value 'change "1" to match th column offset to Years col Case 0 To 4 y = 1 Case 5 To 9 y = 2 Case 10 To 14 y = 3 Case 15 To 19 y = 4 Case Is 19 y = 5 End Select oCell.Offset(0, 2) = Application.Index(Range("Text"), x + y) 'change the "2" to an empty column offset fro SalaryType VOID: Next End Su ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
If statement with multiple conditions
Allow me to clarify the SalaryType column in my above post: In my solution I assumed from your first post that this column will contain intergers- unknown at this point- but you wanted to assign one of two categories to each of these entries. In my code, each entry will be assigned a value of zero if it's 1 to 3 or a value of 5 if the entry is above 3. HTH Paul ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
All times are GMT +1. The time now is 03:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com