formula to convert raw data from several sources into relative tot
 

ok, i've worn myself out on the help pages of Excel 2003 and can't seem to
find what i'm looking for. this seems to be right up Excel's alley, but
maybe i'm too close to the forest to see the trees.

I have mostly numerical evaluation data from several separate sources
pertaining to multiple items that will do the same job. each of the
individual sources use different critera. i would like to create a formula
to convert the individual raw data into a relative total score to assist in
making a decision among items.

For example, one data source may evaluate an item in several categories
using a range of 1 to 5 with 1 being the highest rating. other sources may
use a range of 1 to 10 with 10 as the highest rating and so forth. I would
like to create a formula that would allow me to enter the value from the data
source, but convert it to relative data that would assist in making a
decision. If the data source claims 1 as it's highest rating, i would like
to insert the raw data into a worksheet so that the formula converts the 1 to
a 5, or a 2 to a 4, or a 5 to a 1, and so forth and then includes the
converted data in a total column.

i can't figure out how to do this and would appreciate any help i can get
before i pull out anymore hair? Thanks

formula to convert raw data from several sources into relative tot
 

If your raw data can vary from 1 to 5 and you want this to map to 5
down to 1, all you need is:

= 6 - raw_data

Similarly, if the variation in your second criteria is from 1 to 10:

= 11 - raw_data2

You may need to convert these to a standard measure, e.g. percentages,
if you want to add them together to arrive at a "meaningful" total, or
some other weighting.

Hope this helps.

Pete

Yancy wrote:

ok, i've worn myself out on the help pages of Excel 2003 and can't seem to
find what i'm looking for. this seems to be right up Excel's alley, but
maybe i'm too close to the forest to see the trees.

I have mostly numerical evaluation data from several separate sources
pertaining to multiple items that will do the same job. each of the
individual sources use different critera. i would like to create a formula
to convert the individual raw data into a relative total score to assist in
making a decision among items.

For example, one data source may evaluate an item in several categories
using a range of 1 to 5 with 1 being the highest rating. other sources may
use a range of 1 to 10 with 10 as the highest rating and so forth. I would
like to create a formula that would allow me to enter the value from the data
source, but convert it to relative data that would assist in making a
decision. If the data source claims 1 as it's highest rating, i would like
to insert the raw data into a worksheet so that the formula converts the 1 to
a 5, or a 2 to a 4, or a 5 to a 1, and so forth and then includes the
converted data in a total column.

i can't figure out how to do this and would appreciate any help i can get
before i pull out anymore hair? Thanks

formula to convert raw data from several sources into relative tot
 

Hi Yancy,

You might want to try using the 3rd argument of the MATCH() function.

I often want to convert different numerical scores into Excellent Good
Fair Poor ratings,
and use: =INDEX(PFGE,MATCH(A1,LookUp,Orientation))

Where PFGE is a range of four cells: {"P","F","G","E"}
Lookup is boundaries between grades {-9999,1,2,3} or
{+9999,3,2,1}
Orientation gives sorting order (1) or (-1)

Then INDEX(PFGE,MATCH(2.5,Lookup1,+1)) = "G"
and INDEX(PFGE,MATCH(2.5,Lookup2,-1)) = "F"


Rupert

Yancy wrote:
ok, i've worn myself out on the help pages of Excel 2003 and can't seem to
find what i'm looking for. this seems to be right up Excel's alley, but
maybe i'm too close to the forest to see the trees.

I have mostly numerical evaluation data from several separate sources
pertaining to multiple items that will do the same job. each of the
individual sources use different critera. i would like to create a formula
to convert the individual raw data into a relative total score to assist in
making a decision among items.

For example, one data source may evaluate an item in several categories
using a range of 1 to 5 with 1 being the highest rating. other sources may
use a range of 1 to 10 with 10 as the highest rating and so forth. I would
like to create a formula that would allow me to enter the value from the data
source, but convert it to relative data that would assist in making a
decision. If the data source claims 1 as it's highest rating, i would like
to insert the raw data into a worksheet so that the formula converts the 1 to
a 5, or a 2 to a 4, or a 5 to a 1, and so forth and then includes the
converted data in a total column.

i can't figure out how to do this and would appreciate any help i can get
before i pull out anymore hair? Thanks