Converting a Cell Value to a Different Value

[Freeneasy]

I am trying to develop a decision matrix and I can’t 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

 

[John Bundy]

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.

 

[Freeneasy]

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