Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting a Cell Value to a Different Value
I am trying to develop a decision matrix and I cant figure out how to do it
even tho it seems to be staring me in the face. I have information from several different sources that provide subjective numeric criteria in various categories about the same item, but the format from each source is unique and dissimilar from the other. I want to be able to create a formula that will convert the source data into a useable format that will help in the decision process. For example one source may rate an item from 1 10 with 1 being the most desirable; another source may rate the same item on a scale of 1 -5 with 5 being the best. There are other sources that likewise provide information in a distinct format. None of the sources present the info in a consistent manner. The formula I would like to create will convert a typed number from one source into a uniform score that, when totaled will help make a decision. For example, where in the above 1 -10 example, if a 1 were typed in a cell, the formula might convert the 1 to, say, a value of 5. When totaled the best recommendations from each source would yield the highest number. Any assistance would be greatly appreciated |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting a Cell Value to a Different Value
Not sure all of the types you use but for the two listed and if you know the
min and max in advance, just take the percent of the whole for each and then sum. For example, on the 1 to 10 scale, a selection of 4 is 40 percent of 10, and on a 1 to 5 scale 2 is 40% of 5. Once you convert each to their own percent, sum them up and see what whens. You can also look up a 5 number summary for a good tool for data analysis. -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. "Freeneasy" wrote: I am trying to develop a decision matrix and I cant figure out how to do it even tho it seems to be staring me in the face. I have information from several different sources that provide subjective numeric criteria in various categories about the same item, but the format from each source is unique and dissimilar from the other. I want to be able to create a formula that will convert the source data into a useable format that will help in the decision process. For example one source may rate an item from 1 10 with 1 being the most desirable; another source may rate the same item on a scale of 1 -5 with 5 being the best. There are other sources that likewise provide information in a distinct format. None of the sources present the info in a consistent manner. The formula I would like to create will convert a typed number from one source into a uniform score that, when totaled will help make a decision. For example, where in the above 1 -10 example, if a 1 were typed in a cell, the formula might convert the 1 to, say, a value of 5. When totaled the best recommendations from each source would yield the highest number. Any assistance would be greatly appreciated |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting a Cell Value to a Different Value
thanks for your response, John, but there're about 100 items and the total
criteria may be as many as 20 separate categories with a distinct rating sytem that needs to be converted. rather than manually entering a percentage, i'd like to think a formula could do the work if i were able to set it up so that when the category rating was typed in, it would somehow convert that rating into a standard that relates to the end result. maybe this isn't something Excel can do. anyway, i want to be able to type a variable number from, say 1 to 10 in a cell for anywhere between 5 and 10 cells (categories), a number from 1 to 5 in anywhere from another set of 5 to 10 cells and so forth and have them convert into a relative total. so, rather than manually converting a number to a percentage for upwards of 2,000 entries, i was hoping to create a formula that would allow me to type in the raw number from one rating source (the source may contain 5 or more categories). the problem, i guess, is the variable raw number "John Bundy" wrote: Not sure all of the types you use but for the two listed and if you know the min and max in advance, just take the percent of the whole for each and then sum. For example, on the 1 to 10 scale, a selection of 4 is 40 percent of 10, and on a 1 to 5 scale 2 is 40% of 5. Once you convert each to their own percent, sum them up and see what whens. You can also look up a 5 number summary for a good tool for data analysis. -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. "Freeneasy" wrote: I am trying to develop a decision matrix and I cant figure out how to do it even tho it seems to be staring me in the face. I have information from several different sources that provide subjective numeric criteria in various categories about the same item, but the format from each source is unique and dissimilar from the other. I want to be able to create a formula that will convert the source data into a useable format that will help in the decision process. For example one source may rate an item from 1 10 with 1 being the most desirable; another source may rate the same item on a scale of 1 -5 with 5 being the best. There are other sources that likewise provide information in a distinct format. None of the sources present the info in a consistent manner. The formula I would like to create will convert a typed number from one source into a uniform score that, when totaled will help make a decision. For example, where in the above 1 -10 example, if a 1 were typed in a cell, the formula might convert the 1 to, say, a value of 5. When totaled the best recommendations from each source would yield the highest number. Any assistance would be greatly appreciated |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct issues | Excel Worksheet Functions | |||
Compiling macro based on cell values | Excel Discussion (Misc queries) | |||
Help with this conditional IF statement | Excel Discussion (Misc queries) | |||
resetting last cell | Excel Discussion (Misc queries) | |||
Instead of a negative number, I'd like to show zero... | Excel Worksheet Functions |