CODEWITHSUNDEEP
excelexcel-formula
user1624926
user1624926

Reputation: 451

Closest number with conditions applied

I want to find the closest number to a number I input from a list of number depending on certain conditions. Currently I'm

=SMALL($C$3:$C$16,COUNTIFS($C$3:$C$16,"<"&$E3)+1)
=LARGE($C$3:$C$16,COUNTIFS($C$3:$C$16,">"&$E3)+1)

$E3 is the number I input. C3:c16 is the range of numbers I'm looking to. I take the ABS value of both results and which ever is closest to E3 I take. This works fine. However I want to introduce a condition. If any cell in Range $B3:$B16 contains "Apple" (as example) I want to exclude the number in column C from my calculation. Any help would be really appreciated.

Ciaran.

Upvotes: 1

Views: 69

Answers (1)

Fabian F
Fabian F

Reputation: 319

To solve your problem, you need to use an Array-Formula. The following formulas should work like your functions with the desired criteria added:

{=SMALL(IF($B$3:$B$16<>"Apple",$C$3:$C$16),COUNTIFS($C$3:$C$16,"<"&$E3,$B$3:$B$16,"<>Apple"))}
{=LARGE(IF($B$3:$B$16<>"Apple",$C$3:$C$16),COUNTIFS($C$3:$C$16,">"&$E3,$B$3:$B$16,"<>Apple"))}

Note: You need to press CTRL+SHIFT+ENTER to enter the formula as an array formula.

Upvotes: 1

Related Questions