SOLVED

IFS return #N/A with more criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-2408152%22%20slang%3D%22en-US%22%3EIFS%20return%20%23N%2FA%20with%20more%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2408152%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20All%2FHello%2C%3C%2FP%3E%3CP%3EI%20use%20following%20formula%20which%20brings%20me%20the%20%23%20N%2FA%20result%3C%2FP%3E%3CP%3E%26nbsp%3BIFS%20(%20%24V%2444%20%26gt%3B%20%24O%248%20%2C%20%24X%248%20%2C%20%24V%2444%20%26lt%3B%20%24P%248%20%2C%20%24X%248%20)%3C%2FP%3E%3CP%3EOn%20evaluation%20of%20formula%20V44%20(parameter)%20%26gt%3B%20reference%201%20(O8)%20%3D%20%3CEM%3EFALSE%3C%2FEM%3E%20and%20next%20criteria%20V44%26lt%3B%20ref%202(P8)%20%3D%20FALSE%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20report%20has%20different%20parameters%20which%20need%20to%20bring%20the%20formula%20result%20from%20another%20cell%20(X8).%3C%2FP%3E%3CP%3EV44%20is%20the%20cell%20which%20can%20vary%20from%20one%20row%20to%20another%20and%20calculate%20the%20average.%3C%2FP%3E%3CP%3EK8%20is%20the%20basis%20parameter%2C%20and%20O8%20and%20P8%20are%20the%20the%20next%20parameters%20based%20on%20which%20X8%20is%20calculating%26nbsp%3B%20IFS%20(%200%20%26lt%3B%20(%20%24K%248%20-%20%24V%2444%20)%20%2C%20(%20%24V%2444%20-%20%24K%248%20)%20%2C%200%20%26lt%3B%20%24P%248%26nbsp%3B%200%20)%3C%2FP%3E%3CP%3EIn%20other%20words%2C%20my%20IFS%20formula%20have%203%20criteria%20or%20reference%3A%3C%2FP%3E%3CP%3E1.%20If%20the%20parameter%20(V44)%20is%20between%2077%20and%2076%20it%20shows%20the%201%3A1%20deduction%20below%2077%20-%20this%20works%20very%20well%3C%2FP%3E%3CP%3E2.%20If%20the%20parameter%20(V44)%20is%20below%2076%20but%20%26gt%3B%3D%2072%20should%20bring%20the%20formula%20result%20from%20cell%20X8%20-%20%23N%2FA%3C%2FP%3E%3CP%3E3.%20If%20the%20parameter%20is%20below%2072%20should%20bring%20the%20same%20formula%20result%20from%20cell%20X8%20-%20%23N%2FA%3C%2FP%3E%3CP%3EI'm%20using%20O365%20if%20this%20count%20for%20you.%3C%2FP%3E%3CP%3EPlease%2C%20if%20anyone%20can%20help%20me%20on%20how%20can%20I%20put%20this%20formula%20to%20work%20out%20for%20me%20and%20hope%20my%20explanation%20show%20the%20meaning%20of%20my%20calculation.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2408152%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

Dear All/Hello,

I use following formula which brings me the # N/A result

 IFS ( $V$44 > $O$8 , $X$8 , $V$44 < $P$8 , $X$8 )

On evaluation of formula V44 (parameter) > reference 1 (O8) = FALSE and next criteria V44< ref 2(P8) = FALSE 

My report has different parameters which need to bring the formula result from another cell (X8).

V44 is the cell which can vary from one row to another and calculate the average.

K8 is the basis parameter, and O8 and P8 are the the next parameters based on which X8 is calculating  IFS ( 0 < ( $K$8 - $V$44 ) , ( $V$44 - $K$8 ) , 0 < $P$8  0 )

In other words, my IFS formula have 3 criteria or reference:

1. If the parameter (V44) is between 77 and 76 it shows the 1:1 deduction below 77 - this works very well

2. If the parameter (V44) is below 76 but >= 72 should bring the formula result from cell X8 - #N/A

3. If the parameter is below 72 should bring the same formula result from cell X8 - #N/A

I'm using O365 if this count for you.

Please, if anyone can help me on how can I put this formula to work out for me and hope my explanation show the meaning of my calculation.

1 Reply
best response confirmed by CarmenV (Occasional Contributor)
Solution
I found the solution in a mean time and is working.
Created another row with new formula so the 2nd (middle value) and 3rd (lowest value) criteria can work.
Now my formula is like this =IFS($V$44>=$O$8;$X$8;$V$44<$K$9;$X$9;$V$44>=73;0) ... 73 and higher being the middle value and 2nd criteria.
However, if any another idea comes from you it will be appreciated.