#N/A ?#2

crtas1

Board Regular
Joined
Jul 9, 2002
Messages
55
ALADIN GAVE ME THIS TO USE YESTERDAY TO MAKE ALL #N/A's IN A SPREADSHEET 0.

=IF(ISNA(SETV(VLOOKUP(...))),0,VLOOKUP(...))

I am not sure what to put where the ... are. Ususally my VLOOKUP looks like =VLOOKUP(B2,A1:D1,2,FALSE)
Am I doing (VLOOKUP("N/A",a1:d1,?,false)????
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
put exactly what you'd normally put there! Have you tried it? What results are you getting?

Paddy
This message was edited by PaddyD on 2002-09-11 05:42
 

crtas1

Board Regular
Joined
Jul 9, 2002
Messages
55
What I typed is what I would normally would put in. Where the ? is is the part I am not sure of. In VLOOKUP it usually pulls something back into the cell you have it in. I don't want it to pull anything back so I am not sure what to put there or if the #N/A part is correct either. I am getting formula errors.
This message was edited by crtas1 on 2002-09-11 06:15
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
On 2002-09-11 05:36, crtas1 wrote:
ALADIN GAVE ME THIS TO USE YESTERDAY TO MAKE ALL #N/A's IN A SPREADSHEET 0.

=IF(ISNA(SETV(VLOOKUP(...))),0,VLOOKUP(...))

I am not sure what to put where the ... are. Ususally my VLOOKUP looks like =VLOOKUP(B2,A1:D1,2,FALSE)
Am I doing (VLOOKUP("N/A",a1:d1,?,false)????

I see I goofed... try:

=IF(ISNA(SETV(VLOOKUP(B2,A1:D1,2,0))),0,GETV())

The intention is not to compute twice: That's why we use SETV and GETV from the morefunc add-in.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

Oopps - and I just saw the setv & ploughed straight on in - must be time for bed :)
 

crtas1

Board Regular
Joined
Jul 9, 2002
Messages
55
=IF(ISNA(SETV(VLOOKUP("#N/A",N7:Z12,2,0))),0,GETV())
I did get a 0 back when I entered this in the cell that this formula was in. I want it to put zeros in all the cells that have an #N/A in them. Will I have to put this in front of every cell that could have a #N/A? I already have this in there:

=VLOOKUP(Sheet1!$A$2,$A$1:$E$369,4,FALSE)
which is what is creating the #n/a. If I do need to put yours with mine how would I put them together. I was thinking more like a cell outside of the formulas that would scan the worksheet and change them automatically.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
On 2002-09-11 06:23, crtas1 wrote:
=IF(ISNA(SETV(VLOOKUP("#N/A",N7:Z12,2,0))),0,GETV())
I did get a 0 back when I entered this in the cell that this formula was in. I want it to put zeros in all the cells that have an #N/A in them. Will I have to put this in front of every cell that could have a #N/A? I already have this in there:

=VLOOKUP(Sheet1!$A$2,$A$1:$E$369,4,FALSE)
which is what is creating the #n/a. If I do need to put yours with mine how would I put them together. I was thinking more like a cell outside of the formulas that would scan the worksheet and change them automatically.

Just replace

=VLOOKUP(Sheet1!$A$2,$A$1:$E$369,4,FALSE)

with

=IF(ISNA(SETV(VLOOKUP(Sheet1!$A$2,$A$1:$E$369,4,0))),0,GETV())
 

crtas1

Board Regular
Joined
Jul 9, 2002
Messages
55
That worked perfectly!! I appreciate your help on this. Thanks again!!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,526
Messages
5,602,181
Members
414,510
Latest member
mande358

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
Top