Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF function
Hello,
I am trying to build a function which compares a value with 5 predefined valueranges and when the value falls within one och the five categories the cell should return a specific and unique text. Can I use IF functions and combine för the five different valueranges? If so, how do I combine? Sincerely Jonas |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF function
One way is to create a table of your values and the corresponding text
something like this: Col A Col B 0 Text 1 10 Text 2 20 Text 3 30 Text 4 40 Text 5 The a formula like this:- =VLOOKUP(C1,A1:B5,2,TRUE) This looks for the value in C1 in Column A and returns the corresponding text in Column B. So for 1 to 9 it returns Text 1. For 10 to 19 Text 2 etc. Mike "JonasO" wrote: Hello, I am trying to build a function which compares a value with 5 predefined valueranges and when the value falls within one och the five categories the cell should return a specific and unique text. Can I use IF functions and combine för the five different valueranges? If so, how do I combine? Sincerely Jonas |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF function
"JonasO" wrote in message
... Hello, I am trying to build a function which compares a value with 5 predefined valueranges and when the value falls within one och the five categories the cell should return a specific and unique text. Can I use IF functions and combine för the five different valueranges? If so, how do I combine? Sincerely Jonas The best approach is to use VLOOKUP. As an example, list the 5 predefined numbers (the boundaries between your value ranges) in ascending order in A1:A5 and the corresponding required text in B1:B5. Say the number whose value you want to compare is in G9. Use this formula: =VLOOKUP(G9,A1:B5,2,TRUE) It will return the text corresponding to the predefined number which is less than or equal to the number in G9. This concept can be extended in various ways. For example, if there are gaps between your required ranges, simply make more alternatives with blank cells (or whatever you prefer) corresponding to the 'gap ranges'. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF function
Mike and Stephen,
Many thanks för your quick replies. It worked perfect. Jonas "Stephen" skrev: "JonasO" wrote in message ... Hello, I am trying to build a function which compares a value with 5 predefined valueranges and when the value falls within one och the five categories the cell should return a specific and unique text. Can I use IF functions and combine för the five different valueranges? If so, how do I combine? Sincerely Jonas The best approach is to use VLOOKUP. As an example, list the 5 predefined numbers (the boundaries between your value ranges) in ascending order in A1:A5 and the corresponding required text in B1:B5. Say the number whose value you want to compare is in G9. Use this formula: =VLOOKUP(G9,A1:B5,2,TRUE) It will return the text corresponding to the predefined number which is less than or equal to the number in G9. This concept can be extended in various ways. For example, if there are gaps between your required ranges, simply make more alternatives with blank cells (or whatever you prefer) corresponding to the 'gap ranges'. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions |