#N/A ?#2

[crtas1]

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)????

 

[PaddyD]

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]

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]

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]

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

 

[crtas1]

=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]

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]

That worked perfectly!! I appreciate your help on this. Thanks again!!