Hello,
Can anyone help me fix these formula so that when the number I'm searching for is "higher" than what's in the search parameter I don't get the #NUM! error? I'd be fine with it showing a negative number or zero. Thanks in advance.
=IF(COUNTIF(A1:A5,B1)>=1,A1:A10,LARGE(A1:A5,COUNTIF(A1:A10,">"&B1)))
=IF(COUNTIF(A1:A5,B1)>=1,A1:A10,SMALL(A1:A5,COUNTIF(A1:A10,"<"&B1)))
A1: 5
A2: 8
A3: 9
A4: 7
A5: 1
B1: 11
Last edited by artiststevens; 11-29-2010 at 01:10 AM.
artiststevens,
Try:
=IF(ISERROR(IF(COUNTIF(A1:A5,B1)>=1,A1:A10,LARGE(A1:A5,COUNTIF(A1:A10,">"&B1)))),0,IF(COUNTIF(A1:A5,B1)>=1,A1:A10,LARGE(A1:A5,COUNTIF(A1:A10,">"&B1))))
=IF(ISERROR(IF(COUNTIF(A1:A5,B1)>=1,A1:A10,SMALL(A1:A5,COUNTIF(A1:A10,"<"&B1)))),0,IF(COUNTIF(A1:A5,B1)>=1,A1:A10,SMALL(A1:A5,COUNTIF(A1:A10,"<"&B1))))
Have a great day,
Stan
Windows 10, Excel 2007, on a PC.
If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
Worked like a charm! Thank you very much!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks