#num error

madankum138

New member
Joined
Jan 23, 2013
Messages
2
Reaction score
0
Points
0
wen type this formula i get an num error when ther is now data available in the cell ""=IF(ROWS(A$1:A36)>COUNTA('process standards LHS'!$E$5:$E$500),"",INDEX(('process standards LHS'!$E$5:$E$500),SMALL(IF('process standards LHS'!$E$5:$E$500<>"",ROW('process standards LHS'!$E$5:$E$500)-ROW('process standards LHS'!$E$5)+1),ROWS(A$1:A36))))""
 

Mary

New member
Joined
Jan 18, 2013
Messages
7
Reaction score
0
Points
0
Take a look at the office help for the #NUM:

Occurs with invalid numeric values in a formula or function.

Possible causes and solutions
Using an unacceptable argument in a function that requires a numeric argument

Make sure the arguments used in the function are numbers. For example, even if the value you want to enter is $1,000, enter 1000 in the formula.

Using a worksheet function that iterates, such as IRR or RATE, and the function cannot find a result

Use a different starting value for the worksheet function.

Change the number of times Microsoft Excel iterates formulas.

Entering a formula that produces a number that is too large or too small to be represented in Microsoft Excel

Change the formula so that its result is between and .
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Two options:

1) Evaluate the formula by stepping through it. That should show you what cell area is causing your error. (Formulas-->Evaluate formula-->keep clicking the Evaluate button till you see the error pop up.
2) Upload a copy of the workbook so we can see what is causing it.
 

madankum138

New member
Joined
Jan 23, 2013
Messages
2
Reaction score
0
Points
0
I have attached the file please check and Reply me .
And i have mentioned the problem in the same file in sheet - operator interface . please do check and do the needful

Thank You .
Two options:

1) Evaluate the formula by stepping through it. That should show you what cell area is causing your error. (Formulas-->Evaluate formula-->keep clicking the Evaluate button till you see the error pop up.
2) Upload a copy of the workbook so we can see what is causing it.
View attachment test copy.xlsx
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
The target column you're looking at has "" in it, which is not blank. This means that your COUNTA is counting every row in it. Change that to COUNT and I think you'll be fine.
 
Top