Excel Macro #N/A

kevinb11

Active Member

Joined

Feb 7, 2012

Messages

352

• Feb 9, 2014

• #2

Sam Clough said:

I am doing a Ranking schedule with 2500 ROWS plus the following Columns


When I try to copy and paste Ranking Formula of =IF(D3="","",RANK(D3,$D$3:$D$300,0)) It inserts the apparent correct formula but comes up with #N/A on many of the Rows.

Can't seem to fix.

Anyone help?

Click to expand...

Just by looking at your formula compared to your information your formula is in error for the range. It only goes to 300 but you said you have 2500 rows.

 

Upvote 0

Sam Clough

New Member

Joined

Feb 9, 2014

Messages

44

Office Version

1. 365

Platform

1. Windows

• Feb 9, 2014

• #3

kevinb11 said:

Just by looking at your formula compared to your information your formula is in error for the range. It only goes to 300 but you said you have 2500 rows.

Click to expand...

Sorry, I just used 300 for the exercise. I actually "copy and pasted" right to 2500. Sorry for the confusion.

 

Upvote 0

MARK858

MrExcel MVP

Joined

Nov 12, 2010

Messages

16,175

Office Version

1. 365

Platform

1. Windows
2. Mobile

• Feb 9, 2014

• #4

Is your data imported? I am just suspicious that some of your cells might contain text rather than real numbers.

Put =ISNUMBER(D3) and drag down to see if you get TRUE or FALSE.

 

Upvote 0

Sam Clough

New Member

Joined

Feb 9, 2014

Messages

44

Office Version

1. 365

Platform

1. Windows

• Feb 9, 2014

• #5

Not sure how to use =ISNUMBER(D3). where do I actually key that in.

 

Upvote 0

MARK858

MrExcel MVP

Joined

Nov 12, 2010

Messages

16,175

Office Version

1. 365

Platform

1. Windows
2. Mobile

• Feb 9, 2014

• #6

It's a formula, just put it in a blank column somewhere on row 3 then left click the bottom right hand corner of the cell, keeping the left button of our mouse held down and drag the mouse down the column.

Excel Workbook
	D	E	F
3	17		TRUE
4	21		TRUE
5	25		TRUE
6	29		TRUE
7	33		TRUE
8	36		FALSE
9	41		TRUE
10	45		TRUE
11	49		TRUE
12	53		TRUE
13	57		TRUE
14	61		FALSE
15	13		TRUE
Sheet1

 

Upvote 0

Sam Clough

New Member

Joined

Feb 9, 2014

Messages

44

Office Version

1. 365

Platform

1. Windows

• Feb 9, 2014

• #7

The column with the problem is F3 - should I use =ISNUMBER(F3) ? Using D3 all show FALSE - yet some columns do work correctly.

 

Upvote 0

MARK858

MrExcel MVP

Joined

Nov 12, 2010

Messages

16,175

Office Version

1. 365

Platform

1. Windows
2. Mobile

• Feb 9, 2014

• #8

should I use =ISNUMBER(F3)

Click to expand...

No!
Select Column D, click the data tab, click Text to Columns, then click next twice and then click finish.
Are the results of the ISNUMBER formula now all TRUE?

 

Last edited: Feb 9, 2014

Upvote 0

Sam Clough

New Member

Joined

Feb 9, 2014

Messages

44

Office Version

1. 365

Platform

1. Windows

• Feb 9, 2014

• #9

Yes, All are FALSE - What do I do now.

 

Upvote 0

MARK858

MrExcel MVP

Joined

Nov 12, 2010

Messages

16,175

Office Version

1. 365

Platform

1. Windows
2. Mobile

• Feb 9, 2014

• #10

Did you do the Text to Columns instructions in post #8.
If you did and it is still FALSE, what exactly are in the cells in Column D?

 

Upvote 0