How to use ISERROR in the following Formula ?

RapchikM

Board Regular
Joined
Oct 5, 2020
Messages
97
Office Version
  1. 2021
Platform
  1. Windows
Hello
Formula obtained from Missing Numbers From a Range
=SMALL(IF(COUNTIF($B$3:$D$5, ROW($1:$9))=0, ROW($1:$9), ""), ROWS(B8:$B$8))

How Can i use ISERROR in above syntax/Formula ?

I don't know whether the above link reference is misleading because when going through it I got confused when
i read the following

COUNTIF($B$3:$D$5, ROW($1:$9)

becomes

COUNTIF({1,3,4; 5,6,7; 8,8,3},{1; 2; 3; 4; 5; 6; 7; 8; 9})

and returns

{1;0;2;1;1;1;1;2;0}.
Because 0 or Zero indicates "TRUE" and 1 indicates "FALSE" So where does 2 Come from ?
So Basically i wanted to avoid Error #NUM

Thanks
Rapchik
 
Fluff Sir

Do you have the TOCOL function?
It seems not there. Which version of Excel would have TOCOL function.

The array {1;0;2;1;1;1;1;2;0} shows how many times each number from 1 to 9 occurs in the range.
So 1 occurs once 2 is missing, 3 occurs twice .
Great ! Thanks for the Clarifications. Now very clear so therefore now wrong perception regarding the deriving result of 0,1 and 2.


FJNS
Thank you so much for your file and Formula representations. Indeed very neatly presented with Easy understanding. Really appreciate your hard efforts (y)🥂

Thanks
RapchikM
 
Upvote 0

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
As you don't have tocol, another approach is
Fluff.xlsm
IJKLM
1
21342
35679
4883
Master
Cell Formulas
RangeFormula
M2:M3M2=FILTER(SEQUENCE(9),COUNTIFS(I2:K4,SEQUENCE(9))=0)
Dynamic array formulas.
 
Upvote 0
M2=FILTER(SEQUENCE(9),COUNTIFS(I2:K4,SEQUENCE(9))=0)
Great. Superb Alternative.
Only thing the cell M2 becomes quite Blurred and font changes to Segoe UI from Calibri which i normally use and Size from 11 changed to 8
Also the values drop down Vertically in the M Column from M2 onwards.

Same Effect as above when used your method with TOCOL

Will it be possible to have the values represented in ROW

Rapchik
 
Upvote 0
For horizontal use
Excel Formula:
=FILTER(SEQUENCE(,9),COUNTIFS(I2:K4,SEQUENCE(,9))=0)
The formula does not change the font, or font size.
 
Upvote 0
=FILTER(SEQUENCE(9),COUNTIFS(I2:K4,SEQUENCE(9))=0)
Indeed Excellent.

The formula does not change the font, or font size
Pleas don't misunderstand me. It has changed. if you feel like you may forward these feedback to MS.
It has changed at cell where only formula has been written

Unable to upload image

Regards
RapchikM
 
Upvote 0
Typing a formula into a cell cannot change the format.
 
Upvote 0
REV-IMG_1722-ModifiedMarked.jpg

Sir Image is in front of you with red mark circle
Regds
RapchikM
 

Attachments

  • REV-IMG_1722-ModifiedMarked.jpg
    REV-IMG_1722-ModifiedMarked.jpg
    75 KB · Views: 7
Last edited:
Upvote 0
That image does not tell me anything. Are you just typing the formula, or copy/pasting from here?
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top