Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting 2 colums of numbers and incremening them down | Excel Discussion (Misc queries) | |||
ranking query | Excel Discussion (Misc queries) | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |