#VALUE! appears everytime I try to use a formula on an excel file copied from the internet

[rgrstvr]

I copied an excel file of nursery plant prices.

Then I tried using the PRODUCT formula on the copied file.

But the formula produced the #VALUE! no matter if the copied price format was TEXT or NUMBER or GENERAL OR CURRENCY. And if the formula is =PRODUCT(C3,1.2) no matter what price was in space C3 - the answer comes up as 1.2

If I empty the space C3 and then retype the price in that space - the formula works properly.

But I have too many prices to retype them all.

[Moo the Dog]

Not sure why you are using PRODUCT, when you can just use =C3*1.2... nonetheless, this error occurs when the prices you are multiplying are stored as TEXT values, instead of NUMBERS. If you select the column of prices, a notification with an exclamation point should pop up next to the top cell in the selected range to tell you that there are numbers stored as text. Click on that exclamation point and choose 'Convert to Number'.

Hope this helps.

- Moo

[FDibbins]

to add to what moo said, changing the format of a cell does not change the actual contents of the cell, it just changes the cosmetics. so, if a cell contains text, formatting it as NUMBER will not change the fact that it still contains text

[rgrstvr]

When I select the column of prices no notification pops up next to the top of the cell.

Everything on the sheet indicates the column of prices are numbers.

I copy the column and convert it to numbers but it still does not work.

If the column contains invisible text I do not know how to get rid of it.

Thanks for your effort.

[oeldere]

Probably you get better help if you add an excel file, without confidentional information.

[FDibbins]

a quick test to see if you are working with values or text looking like values is to use =isnumber(cell-ref) TRUE for number, FALSE for text

[rgrstvr]

I tried the =isnumber(cell-ref) on the list of prices copied from the internet and it says they are NOT numbers.

So what do I do now?

[Moo the Dog]

Upload a copy of your spreadsheet with any sensitive data changed/removed?

[FDibbins]

you can highlight the entire range of "numbers", then scroll back up to the top and look for a little yellow diamond on the left of the top highlighted cell. click on that and select "convert text to numbers"

[rgrstvr]

I do not know how to download the file to the forum.

I have already highlighted the numbers and no little yellow diamond appears at the top.

[martindwilson]

Attach a sample workbook.

When you are in Advanced reply, click on the paperclip to open the upload window.

View Pic

[FDibbins]

if they are indeed text that looks like numbers, then maybe you didnt high-light the right range. make sure you select the very 1st cell (that should show the yellow diamond), then highlight all the way down to the bottom of that range.

[rgrstvr]

i repeat - there is no "little yellow diamond"

[rgrstvr]

I am going to try to add the sheet in question to this message

4 Geo Sparkman our format+.xlsx

[FDibbins]

i repeat - there is no "little yellow diamond"

hmmm just about EVERY cell on that sheet will show you a "little yellow diamond" if you click on it!!

if you see the little green flag in a cell, it means there is an error of some kind in that cell

[Moo the Dog]

Most of the "numbers" in column B are actually stored as text, as I suspected. And the prices in column C are all text since the $ was manually added.

See my attached modified worksheet.

- Moo

Note - you will still see a #VALUE error in the cells where you have a price as well as text in the cells (for instance "12 (Minimum 3)")

[rgrstvr]

hmmm just about EVERY cell on that sheet will show you a "little yellow diamond" if you click on it!!

if you see the little green flag in a cell, it means there is an error of some kind in that cell

I am using Excel 2010 and there are NO "little yellow diamonds" when I click on ANY cell on that sheet.

[FDibbins]

I doubt that 2010 is any different to 2007 in that regard, but you may just have error checking disabled (excel options/formulas/error checking).

however, that is pretty much irrelevant. As was pointed out above (post 16) most of the entries in columns B:C are text that looks like values.

Im sure that Moo has either corrected that for you, or shown you how to correct it

[Moo the Dog]

Not only did I correct the errors in my attached sheet above (Post 16), but here is a screenshot of what I see when I open your original file that you uploaded.

Number Stored as Text Errors Everywhere.jpg

I am sure FDibbins sees the same thing as I do. If you don't see all those green triangles and the error marker, then you must have error-checking turned off. You can check/change this by going to:

Excel Options >> Formulas >> Error Checking and make sure 'Enable Background Error Checking' is checked, as well as all of the 'Error Checking Rules' should be checked (right below it).

- Moo

<humor> And not to nit pick but... Comic Sans? </humor>

[oeldere]

I used this code to change text into numbers.

Please Login or Register  to view this content.