• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

#NUM! Error

P

Pushppreet

New Member
Hi,

I am getting #NUM! error in my worksheet "Monthly Trng View" in B32, B33 and B34 and I can figure out why.

Can someone help me with this and even explain what is this error all about.

Regards,
Pushppreet
 

Attachments

  • dnata CS Training Plan - 2017 - Pushppreet (Error in file).xlsx
    245.7 KB · Views: 7
Hi Pushppreet,

This is due to the feature of Small function if you investigate your formula output using f9. there is no 32nd numeric value in small function array
16 17 19 20 21 23 24 25 26 27 28 29 32 34 35 36 37 39 40 41 42 44 45 46 47 49 50 53 54 55 56​
FALSE}
FALSE ## ## ## ## FALSE ## ## ## ## FALSE ## ## FALSE FALSE ## ## ## ## FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE​







Pushppreet said:
Hi,

I am getting #NUM! error in my worksheet "Monthly Trng View" in B32, B33 and B34 and I can figure out why.

Can someone help me with this and even explain what is this error all about.

Regards,
Pushppreet
Click to expand...
 
Hi,

Haven't checked thoroughly, but I think you've missed the absolute reference $ sign, highlighted in Red:

=IF(COUNTIF(Calendar!C$4:C$100,">0")<=ROWS($A$6:A6),"",INDEX(Calendar!$A$1:$A$100,SMALL(IF(Calendar!$C$4:$C$100>0,ROW(Calendar!$A$4:$A$100)),ROW(A1))))

Regards,
 
Khalid NGO said:
Hi,

Haven't checked thoroughly, but I think you've missed the absolute reference $ sign, highlighted in Red:
=IF(COUNTIF(Calendar!C$4:C$100,">0")<=ROWS($A$6:A6),"",INDEX(Calendar!$A$1:$A$100,SMALL(IF(Calendar!$C$4:$C$100>0,ROW(Calendar!$A$4:$A$100)),ROW(A1))))

Regards,
Click to expand...
Dear Khalid,

Can you help me in fixing this please..!

Regards,
Pushppreet
 

Attachments

  • dnata CS Training Plan - 2017 - Pushppreet (Error in file).xlsx
    245.7 KB · Views: 3
xlstime said:
Hi Pushppreet,

This is due to the feature of Small function if you investigate your formula output using f9. there is no 32nd numeric value in small function array
16 17 19 20 21 23 24 25 26 27 28 29 32 34 35 36 37 39 40 41 42 44 45 46 47 49 50 53 54 55 56​
FALSE}
FALSE ## ## ## ## FALSE ## ## ## ## FALSE ## ## FALSE FALSE ## ## ## ## FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE​
Click to expand...
Hey Hi,

How can I Solve this. I am not so good in excel and cant figure out what should be done. I have attached the file, could you please help me with this..?

Appreciate your help.

Regards,
Pushppreet
 

Attachments

  • dnata CS Training Plan - 2017 - Pushppreet (Error in file).xlsx
    245.7 KB · Views: 2
Pushppreet said:
Can you help me in fixing this please..!
Click to expand...

Hi Pushppreet,

You have probably not understood what I said in post # 3.

Just add the dollar sign $ (as highlighted in Red) to make your range absolute:

=IF(COUNTIF(Calendar!$C$4:$C$100,">0")<=ROWS($A$6:A6),"",INDEX(Calendar!$A$1:$A$100,SMALL(IF(Calendar!$C$4:$C$100>0,ROW(Calendar!$A$4:$A$100)),ROW(A1))))

Or see the attached.

Regards,
 

Attachments

  • dnata CS Training Plan - 2017 - Pushppreet (Error in file) (1).xlsx
    249.7 KB · Views: 2
Hi ,

To add to what has already been posted , your formula in cell B6 in the tab named Monthwise Trng View is :

=IF(COUNTIF(Calendar!C4:C100,">0")<=ROWS($A$6:A6),"",INDEX(Calendar!$A$1:$A$100,SMALL(IF(Calendar!$C$4:$C$100>0,ROW(Calendar!$A$4:$A$100)),ROW(A1))))

What this is supposed to do is :

1. Get the number of entries in column C , in the range C4:C100 , that are greater than zero. Suppose this is 31.

2. If the number of rows , starting from 1 on row 6 , exceeds the number derived in 1 above , say 31 , then insert blanks , otherwise insert the value returned by the formula :

INDEX(Calendar!$A$1:$A$100,SMALL(IF(Calendar!$C$4:$C$100>0,ROW(Calendar!$A$4:$A$100)),ROW(A1)))

What the above formula does is it returns the first row which has a positive non-zero value in column C ; thereafter , in succession , it will return the second row , the third row and so on ; when there are no more rows which have a positive non-zero value in column C , which will happen when we come to the 32nd row , the SMALL function will return the #NUM! error value , which is what is being displayed.

All rows beyond the 31st row will return this error value.

It is to ensure that this calculation which will return the #NUM! error value does not happen , that the IF function has been used , so that for all rows after the 31st row , the IF function will insert a blank.

However , this has not worked because the initial test within the IF function , which uses the COUNTIF function , has not used absolute referencing as follows :

COUNTIF(Calendar!C$4:C$100,">0")

where the $ signs highlighted in RED , signify absolute addressing for the row references.

In the absence of absolute referencing , when the formula is copied down , the above portion keeps changing as shown below :

COUNTIF(Calendar!C4:C100,">0")

COUNTIF(Calendar!C5:C101,">0")

COUNTIF(Calendar!C6:C102,">0")

COUNTIF(Calendar!C7:C103,">0")

This is bound to return the wrong values , depending on what is present in cells C101 , C102 , C103 ,....

which is why only 3 cells display the #NUM! error values , while the ones beyond display blanks.

Inserting the $ sign appropriately will ensure that only blanks are displayed from row 32 downwards.

Narayan
 
You must log in or register to reply here.
Back
Top