VLOOKUP #N/A Sometimes but not always

[Guest]

Hey all,

I have an assignment I'm working on for school and I have a formula using
VLOOKUP. In the assignment, we're looking up a discount rate based on the
customer's credit score. The credit scores are in the first column and the
discount rates are in the next four columns (the region number - 109 yields a
column number of 2 through 5, which explains the $D22-109 part of the
formula). Here is the formula:

=ROUND(IF($C22=0,0,((VLOOKUP($C22,$A$7:$E$13,$D22-109,TRUE))*$D$16)*$E22), 2)

I've gotten it to work using "487" instead of the $C22, so I thought maybe
the type of data in the cell would make a difference, but that doesn't seem
to change anything. All cells are numbers, so I thought it should work.
There is no difference (that I can see) from the rows that work and the rows
that don't. Any ideas would be greatly appreciated!

 

[Niek Otten]

Probably not all C cells are numbers, although they may look like numbers.
You can test this using the USNUMBER() function and you can correct it:
Format a blank cell as Number. Enter 1. Edit>Copy. Select your C cells.
Edit, Paste Special, check Multiply. OK.

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

 

[Dave Peterson]

Typo alert...

USNUMBER()
should be:
IsNumber()

 

[Guest]

You hit the nail on the head. Why would they all look like they were numbers
when I highlight them and go to "format cells" but obviously they weren't?

Is there any way for a cell to be formatted as a number but display nothing
(i.e. don't show the 0)?

Thanks for all your help!

Matt

 

[Dave Peterson]

If you format a cell as text, then the value typed into that cell will be
treated as text--even if you type digits. If you start your entry with an
apostrophe ('123), then that will be treated as text.

It's very nice if you were typing a string of numbers that won't ever be
added--Social security numbers, credit card numbers and the like.

By the way, if those "text numbers" cells were just changed to Genera (or
another number format), then that's the not enough to change the value in the
cell. That's why Niek suggested the copy|paste special bit.

And you could hide 0's by using
format|conditional formatting
(If it's equal to 0, make the font color match the fill color)

or you could use a custom format of:
0;-0;;@
positive numbers;negative numbers;zero;text

 

[Niek Otten]

Thanks, Dave!

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

 

[Guest]

Anouther way to get rid of the 0s is through the TOOLS-OPTIONS-VIEW-WINDOWS
OPTIONS-0 OPTIONS checkbox. This works on the whole sheet for all I know. We
hide all the 0s.

PS. The last two menu items are a part of the OPTIONS window at the bottom.

GL

 

[Guest]

it is a stupid question but how do you make a cell in excel a zero when it is
a negative calculation and then that same cell to display the positive numbers