Wrong Data Type - perplexing error!

[Helix242]

Hi all,

First off, I hope you, your family, friends and colleagues are all keeping well during this difficult time.

So I have a number on A2, which is produced using the =CELL+CELL formula. I then want to MULTIPLY this by B2, however in B2 there is a VLOOKUP formula (dependent on the number in A2), and Excel doesn't like this - producing the Wrong Data Type error.

If I delete the VLOOKUP formula, and just put the response in there - say 24, then this works. Is there anyway I can make this work with the VLOOKUP formula in play, as this will always change as the number in A2 is not static.

This might appear easy to most of you, but I am struggling

Many thanks in advance!

 

[RoryA]

What exactly is the VLOOKUP formula?

 

[Helix242]

What exactly is the VLOOKUP formula?

=VLOOKUP(D3,A3:B31,2,0)

A2, in my initial enquiry is actually in D3 (I was hoping it would be a bit easier to explain - however I know that may have just caused a bit of confusion!) ?

 

[RoryA]

And are you sure that what is coming back is a number, not text? If you enter =ISNUMBER(B2) does it return TRUE?

 

[Helix242]

And are you sure that what is coming back is a number, not text? If you enter =ISNUMBER(B2) does it return TRUE?

Okay, it returns FALSE - The cell is set as a number, but I'm guessing this is not the way to resolve this?

 

[joeu2004]

So I have a number on A2, which is produced using the =CELL+CELL formula. I then want to MULTIPLY this by B2, however in B2 there is a VLOOKUP formula (dependent on the number in A2), and Excel doesn't like this - producing the Wrong Data Type error.

First, Excel does not return text errors like "Wrong Data Type". (At least, not Excel 2010.) VBA does. Are you really writing VBA code?! If not, what does Excel truly return: #VALUE?

Even if VLOOKUP returns text, a formula of the form =A2*B2 would work because Excel would convert the text to a numeric value -- if it can!

The problem is: Excel does not recognize the value returned by VLOOKUP as a number.

So the question is: what exactly is returned by VLOOKUP? But beware: looks alone can be deceiving.

Be sure that LEN(B2) is the same as the number of characters that you see. If not, it is very likely that there are invisible characters that Excel does not like. Regular spaces are usually okay. But a non-breaking space (ASCII 160) is not.

It would be nice to fix the values in column B. But as quick-and-dirty workaround, try:

=--SUBSTITUTE(VLOOKUP(D3,A3:B31,2,0), CHAR(160), "")

That removes non-breaking spaces and converts the text to a numeric value.

If that still returns #VALUE, something else is wrong. We cannot debug at arm's-length. Upload an example Excel file to a file-sharing website, and post the download URL.

-----

PS....

The cell is set as a number, but I'm guessing this is not the way to resolve this?

No. Setting the format to Number after entering the data does not change the type of the data, if it is text to begin with.

Moreover, if the data is text that Excel cannot recognize as a number, the numeric format of the cell does not matter.

 

[Helix242]

First, Excel does not return text errors like "Wrong Data Type". (At least, not Excel 2010.) VBA does. Are you really writing VBA code?! If not, what does Excel truly return: #VALUE?

Even if VLOOKUP returns text, a formula of the form =A2*B2 would work because Excel would convert the text to a numeric value -- if it can!

The problem is: Excel does not recognize the value returned by VLOOKUP as a number.

So the question is: what exactly is returned by VLOOKUP? But beware: looks alone can be deceiving.

Be sure that LEN(B2) is the same as the number of characters that you see. If not, it is very likely that there are invisible characters that Excel does not like. Regular spaces are usually okay. But a non-breaking space (ASCII 160) is not.

It would be nice to fix the values in column B. But as quick-and-dirty workaround, try:

=--SUBSTITUTE(VLOOKUP(D3,A3:B31,2,0), CHAR(160), "")

That removes non-breaking spaces and converts the text to a numeric value.

If that still returns #VALUE, something else is wrong. We cannot debug at arm's-length. Upload an example Excel file to a file-sharing website, and post the download URL.

-----

PS....


No. Setting the format to Number after entering the data does not change the type of the data, if it is text to begin with.

Moreover, if the data is text that Excel cannot recognize as a number, the numeric format of the cell does not matter.

Thank you for the detailed response, and the insight in to the issues/potential issues of using the correct syntax - this is always extremely helpful.

I think I know what the issue is (after reading your reply!!). The numbers in my lookup table are actually fractions - so I have 1/60, 1/47.64, 1/57 as so on - so A2 would be multiplied by 1/60th etc - As light dawns on marble head over here, I realise that this is NOT the correct syntax for excel to calculate this type of input. I change one of them to a whole number and the calculation worked.

Could I ask if you know how I would calculate a number multiplied by a fraction as the correct syntax??

Thank you!!

 

[joeu2004]

The numbers in my lookup table are actually fractions - so I have 1/60, 1/47.64, 1/57 as so on [....] Could I ask if you know how I would calculate a number multiplied by a fraction as the correct syntax??

When I enter 1/60, that is interpreted as a date. But that will depend on regional and language settings.

If you are entering the data manually, use a formula. In other words, prefix the "fractions" with "=", to wit: =1/60 , =1/47.64 , =1/57 , etc.

You can format the cells as Fraction with an appropriate subtype. But 1/47.64 will never look like 1/47.64.

BTW, is the denominator really 47.64? Or is that an approximation? If the latter, it would be better to enter the exact fraction, if it is indeed a so-called "common" fraction (integer over integer).

 

[Helix242]

When I enter 1/60, that is interpreted as a date. But that will depend on regional and language settings.

If you are entering the data manually, use a formula. In other words, prefix the "fractions" with "=", to wit: =1/60 , =1/47.64 , =1/57 , etc.

You can format the cells as Fraction with an appropriate subtype. But 1/47.64 will never look like 1/47.64.

BTW, is the denominator really 47.64? Or is that an approximation? If the latter, it would be better to enter the exact fraction, if it is indeed a so-called "common" fraction (integer over integer).

Thank you, I will try this in the morning. That is the denominator, there are a few like that in the lookup table.

 

[Helix242]

When I enter 1/60, that is interpreted as a date. But that will depend on regional and language settings.

If you are entering the data manually, use a formula. In other words, prefix the "fractions" with "=", to wit: =1/60 , =1/47.64 , =1/57 , etc.

You can format the cells as Fraction with an appropriate subtype. But 1/47.64 will never look like 1/47.64.

BTW, is the denominator really 47.64? Or is that an approximation? If the latter, it would be better to enter the exact fraction, if it is indeed a so-called "common" fraction (integer over integer).

that worked, and it looks like the correct responses are being shown!

thanks to all of you for your help with this, and for bettering my understanding of Excel, and the correct syntax!