Multiple IF statements for numerical scoring

%3CLINGO-SUB%20id%3D%22lingo-sub-3477817%22%20slang%3D%22es-ES%22%3EMultiple%20IF%20statements%20for%20numerical%20scoring%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3477817%22%20slang%3D%22es-ES%22%3E%3CP%3EJin%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20really%20appreciate%20if%20someone%20help%20me%20with%20the%20following%20goal%3A%3C%2FP%3E%3CP%3E-%20I%20want%20to%20obtain%20three%20scores%20(10%2C%205%20or%201)%20according%20to%20the%20following%20results%20for%20the%20differences%20between%20two%20cells%3A%3C%2FP%3E%3CP%3E1)%20The%20absolute%20difference%20is%20between%200%20and%2020%20inclusive%20-%20score%2010%3C%2FP%3E%3CP%3E2)%20The%20absolute%20difference%20is%20between%2020%20and%2040%20inclusive%20-%20score%205%3C%2FP%3E%3CP%3E3)%20The%20absolute%20difference%20is%20superior%20to%2040%20-%20score%201%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20put%20the%20formula%3A%20%3DIF(ABS(I2-H2)%26lt%3B%3D20%2C%2010)%20OR(IF(AND(ABS(I2-H2))%26gt%3B20%2C%20ABS(I2-H2)%26lt%3B%3D40)%2C%205)%20OR(IF(ABS(I2-H2)%26gt%3B40%2C%201)).%20But%20it%20didn't%20work.%20Could%20you%20please%20correct%20it%2Fsuggest%20another%20one%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance!%3C%2FP%3E%3CP%3EHelena%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3477817%22%20slang%3D%22es-ES%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-3478099%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20IF%20statements%20for%20numerical%20scoring%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3478099%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1416571%22%20target%3D%22_blank%22%3E%40Helena_BG%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(ABS(I2-H2)%26lt%3B%3D20%2C%2010%2CIF(AND(ABS(I2-H2)%26gt%3B20%2C%20ABS(I2-H2)%26lt%3B%3D40)%2C%205%2C%201))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EMaybe%20with%20this%20formula.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22numerical%20scoring.JPG%22%20style%3D%22width%3A%20935px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22numerical%20scoring.JPG%22%20style%3D%22width%3A%20935px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F378240i83197A0C9AE20108%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22numerical%20scoring.JPG%22%20alt%3D%22numerical%20scoring.JPG%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi,

 

I would really appreciate if someone help me with the following goal:

- I want to obtain three scores (10, 5 or 1) according to the following results for the differences between two cells:

1) The absolute difference is between 0 and 20 inclusive - score 10

2) The absolute difference is between 20 and 40 inclusive - score 5

3) The absolute difference is superior to 40 - score 1

 

I put the formula: =IF(ABS(I2-H2)<=20, 10) OR(IF(AND(ABS(I2-H2))>20, ABS(I2-H2)<=40), 5) OR(IF(ABS(I2-H2)>40, 1)). But it didn't work. Could you please correct it/suggest another one?

 

Thank you in advance!

Helena

1 Reply

@Helena_BG 

=IF(ABS(I2-H2)<=20, 10,IF(AND(ABS(I2-H2)>20, ABS(I2-H2)<=40), 5, 1))

Maybe with this formula.

numerical scoring.JPG