Forum Discussion
IFS return #N/A with more criteria
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.
- 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.
1 Reply
- CarmenVCopper ContributorI 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.