excelexcel-formulaz-score

Copy the value into another column if one column value matches to the condition, else paste the next matching value

I want to copy the values from column A to column C only if the Z scores in column B are within the range of -2.68 and 2.68, and I do not want any empty cells in column C. I have tried filter formula, but giving an #CALC! error. Column A values may vary so I want to read column till end of the row condition.

=FILTER(A2:A35,AND(B2:B35>-2.68,B2:B35<2.68))
Time1 Z score Time2 Z score Time3
2812 3.508 1024 0.461
1024 0.461 1772 1.736
1772 1.736 343 -0.699
343 -0.699 436 -0.541
436 -0.541 419 -0.570
419 -0.570 245 -0.866
245 -0.866 287 -0.795
2468 2.922 1312 0.952
287 -0.795 894 0.240
1312 0.952 642 -0.190
894 0.240 898 0.246
642 -0.190 752 -0.002
898 0.246 406 -0.592
752 -0.002 479 -0.468
406 -0.592 433 -0.546
479 -0.468 377 -0.641
433 -0.546 805 0.088
377 -0.641 837 0.142
805 0.088 391 -0.618
837 0.142 417 -0.573
391 -0.618 372 -0.650
417 -0.573 1279 0.896
372 -0.650 453 -0.512
1279 0.896 313 -0.750
453 -0.512 631 -0.209
313 -0.750 554 -0.340
631 -0.209 881 0.217
554 -0.340 491 -0.447
881 0.217 350 -0.687
491 -0.447 477 -0.471
350 -0.687 865 0.190
477 -0.471 502 -0.428
865 0.190
502 -0.428

picture shows how the values required in next column skipping the unmatched values to the condition

After implement this formula

=FILTER(Table1,ABS(Table1[Z score])<2.68)

it is showing result as below in the picture. and a third column E is created, not about this.

results image

Solution

  • Use multiplication for AND logic in FILTER:

    =FILTER(A2:B35,(B2:B35>-2.68)*(B2:B35<2.68))

    enter image description here

    Or use ABS:

    =FILTER(A2:B35,ABS(B2:B35)<2.68)

    enter image description here