Excel - Return values based on multiple criteria in an input cell

%3CLINGO-SUB%20id%3D%22lingo-sub-2079819%22%20slang%3D%22en-US%22%3EExcel%20-%20Return%20values%20based%20on%20multiple%20criteria%20in%20an%20input%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2079819%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%20I%20am%20looking%20for%20a%20bit%20of%20help%20in%20regards%20to%20returning%20numbers%20from%20a%20set%20of%20values%20in%20a%20column.%20It%20is%20complicated%20to%20use%20just%20an%20IF%20function%20as%20it%20involves%20multiple%20criteria.%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20ex.%2C%20I%20would%20like%20set%20up%20a%20formula%2Fmacro%20such%20that%20the%20output%20field%20to%20auto-populate%20based%20on%20the%20following%20criteria%3A%201)%20the%20smallest%20number%20in%20the%20input%20filed%20gets%20the%20biggest%20value%2C%20i.e.%209%3B%20the%20next%20smallest%20gets%207%2C%20and%20so%20on%20in%20decrements%20of%202%3B%202)%20if%20there%20are%20identical%20values%20in%20the%20input%20field%2C%20then%20they%20would%20have%20an%20averaged%20output.%26nbsp%3B%20I'd%20appreciate%20any%20insights%20on%20what%20functions%2Fapproach%20would%20be%20appropriate%20for%20this.%26nbsp%3B%20TIA%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22134%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2267%22%20height%3D%2219%22%3EInput%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E%3CFONT%20color%3D%22%23000000%22%3EOutput%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E7%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23000000%22%3E5%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E11%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23000000%22%3E1%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E5%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23000000%22%3E7%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E9%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23000000%22%3E3%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E3%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23000000%22%3E9%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2079819%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2079912%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20Return%20values%20based%20on%20multiple%20criteria%20in%20an%20input%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2079912%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F936541%22%20target%3D%22_blank%22%3E%40mahalinr%3C%2FA%3E%26nbsp%3BDepending%20on%20your%20Excel%20version%2C%20perhaps%20one%20of%20the%20attached%20solutions%20gives%20you%20what%20your%20ask%20for.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello,  I am looking for a bit of help in regards to returning numbers from a set of values in a column. It is complicated to use just an IF function as it involves multiple criteria. 

For ex., I would like set up a formula/macro such that the output field to auto-populate based on the following criteria: 1) the smallest number in the input filed gets the biggest value, i.e. 9; the next smallest gets 7, and so on in decrements of 2; 2) if there are identical values in the input field, then they would have an averaged output.  I'd appreciate any insights on what functions/approach would be appropriate for this.  TIA

InputOutput
75
111
57
93
39
1 Reply

@mahalinr Depending on your Excel version, perhaps one of the attached solutions gives you what your ask for.