IF, IFS or maybe some other formula?

%3CLINGO-SUB%20id%3D%22lingo-sub-2825568%22%20slang%3D%22en-US%22%3EIF%2C%20IFS%20or%20maybe%20some%20other%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2825568%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20probably%20pretty%20easy%2C%20but%20I%20can't%20make%20it%20work%2C%20been%20trying%20different%20formulas%20without%20success%2C%20and%20now%20I%20need%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20one%20column%20I%20have%20values%20raging%20from%20-3%2C00%20to%203%2C00.%20In%20the%20other%20coulmn%20I%20have%20the%20interpretation%20of%20this%20value%2C%20everything%20between%20-0%2C5%20and%200%2C5%20is%20for%20example%20average%2C%200%2C5%20to%201%20is%20above%20average%20and%20so%20on.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20has%20to%20be%20a%20way%20to%20automate%20this%20interpretation-process%20with%20a%20formula%2C%20right%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20any%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2825568%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2825777%22%20slang%3D%22en-US%22%3ERe%3A%20IF%2C%20IFS%20or%20maybe%20some%20other%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2825777%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1179380%22%20target%3D%22_blank%22%3E%40MATWS%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20you%20may%20provide%20small%20sample.%20It's%20not%20clear%2C%20at%20least%20for%20me%2C%20which%20average%20to%20take%20and%20what%20%22so%20on%22%20means.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2825796%22%20slang%3D%22en-US%22%3ERe%3A%20IF%2C%20IFS%20or%20maybe%20some%20other%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2825796%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1179380%22%20target%3D%22_blank%22%3E%40MATWS%3C%2FA%3E%26nbsp%3BPerhaps%20the%20attached%20file%20will%20get%20you%20started.%20Use%20a%20lookup%20table%20where%20you%20set%20the%20lower%20boundary%20for%20each%20rating.%20In%20the%20example%20I've%20used%20LOOKUP%2C%20but%20you%20might%20as%20well%20try%20VLOOKUP%20or%20XLOOKUP%20(if%20your%20Excel%20version%20supports%20the%20latter).%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2826060%22%20slang%3D%22en-US%22%3ERe%3A%20IF%2C%20IFS%20or%20maybe%20some%20other%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2826060%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOf%20course%2C%20I'm%20sure%20I've%20described%20it%20poorly%20...%20%3CSPAN%3EPerhaps%26nbsp%3BI%20can%20explain%20it%20better!%20I%20can%20upload%20a%20sample%2C%20but%20let%20me%20try%20do%20describe%20it%20better%20first!%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EMy%20values%20can%20be%20anywhere%20between%20%2B3%20and%20-3%20(z-scores).%20For%20every%20value%20there%20is%20a%20description%2Finterpretation.%20Instead%20of%20having%20to%20look%20up%20every%20value%20in%20a%20chart%2C%20for%20example%20this%20one%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.ritenour.k12.mo.us%2Fcms%2Flib011%2FMO01910124%2FCentricity%2FDomain%2F69%2FPsychometric_Conversion_Table.pdf%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EPsychometric_Conversion_Table.pdf%20(ritenour.k12.mo.us)%3C%2FA%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E..%20I%20would%20like%20Excel%20to%20do%20it%20for%20me!%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EDoes%20it%20make%20sense%3F%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

This is probably pretty easy, but I can't make it work, been trying different formulas without success, and now I need help!

 

In one column I have values raging from -3,00 to 3,00. In the other coulmn I have the interpretation of this value, everything between -0,5 and 0,5 is for example average, 0,5 to 1 is above average and so on.

 

There has to be a way to automate this interpretation-process with a formula, right?

 

Thanks in advance for any help!

4 Replies

@MATWS 

Perhaps you may provide small sample. It's not clear, at least for me, which average to take and what "so on" means.

@MATWS Perhaps the attached file will get you started. Use a lookup table where you set the lower boundary for each rating. In the example I've used LOOKUP, but you might as well try VLOOKUP or XLOOKUP (if your Excel version supports the latter). 

@Sergei Baklan 

 

Of course, I'm sure I've described it poorly ... Perhaps I can explain it better! I can upload a sample, but let me try do describe it better first!

 

My values can be anywhere between +3 and -3 (z-scores). For every value there is a description/interpretation. Instead of having to look up every value in a chart, for example this one: Psychometric_Conversion_Table.pdf (ritenour.k12.mo.us)

 

.. I would like Excel to do it for me!

 

Does it make sense?

Thank you! That would probably work out very well, I'm gonna try it out!