What is #NUM!

**MikeNZ** · 08-11-2009, 02:09 PM

I have two questions.

1. What does #NUM! mean when it appears in a cell?

2. How can I get rid of it or hide it so when it doesn't print?

Your help is much appreciated.

**NBVC** · 08-11-2009, 02:16 PM

According to excel this occurs when...

Occurs with invalid numeric values in a formula or function.

What is your formula and what data is it referencing?

**MikeNZ** · 08-11-2009, 02:20 PM

Thanks for the quick reply. The formula is:

=IF(ROWS($C$23:C23)<=$M$4,INDEX(Main!$C$65:$C$84,SMALL(IF(Main!$M$65:$M$84="A",ROW(Main!$C$65:$C$84)-ROW(Main!$C$65)+1),ROWS($C$23:C23))),"")

It is on sheet 2 of two sheets.

**NBVC** · 08-11-2009, 02:22 PM

This is an array formula and must be confirmed with CTRL+SHIFT+ENTER keys at once, to get { } brackets to appear around them... did you do that, then copy down?

**MikeNZ** · 08-11-2009, 02:27 PM

Yes. Copied down to five rows.

**NBVC** · 08-11-2009, 02:33 PM

Maybe posting a workbook might help resolve more quickly.

**MikeNZ** · 08-11-2009, 02:57 PM

Is there a way to format the cell to hide it? I know I've done it when #N/A is shown by formatting the text to be the same as the fill colour.

**NBVC** · 08-11-2009, 03:22 PM

Getting that error with that formula means something is off....

What formula do you have in M4 and is it giving the correct result...

Are your values in Main!$M$65:$M$84 matching with result in M4...

**DonkeyOte** · 08-11-2009, 03:28 PM

You will as NBVC says get #NUM error if M4 > number of matches of A in M65:M84 and the row in which the formula resides references a SMALL k value > no. of instances of A.

**MikeNZ** · 08-11-2009, 03:58 PM

Have just checked the formula in M4 and that was the problem. After a quick tweak all is working perfectly.

Thanks for your replies and help, it is much appreciated.