Impossible to calculate anything in cells. Getting #VALUE error all over the table.

Kizuna

New Member
Joined
Dec 31, 2016
Messages
8
Hello,

I'm trying to calculate the product =100*A3 , but I get the #VALUE error. What's more, I'm getting this error in all cells in which I'm trying to do some sort of mathematical operation. I have tried setting the cells to number value mode, as well as using =PRODUCT(100,A3) (in fact, I'm using Excel in Spanish, so it would be: =PRODUCTO(100,A3) ). Besides, the error is not due to having a space before the value, either. The most weird thing is that this problem just happens in two of the four sheets of my Excel file. That's why I have also tried checking whether the cell format is different in each sheet, but it appears to be the same in all of them.

Moreover, although I have checked some websites regarding this issue, I feel they don't have the answer to my problem. Here are some of the links I have had a look at (the others are in Spanish, so I don't think it's worth posting them here):

https://support.office.com/en-us/ar...UE-error-15e1b616-fbf2-4147-9c0b-0a11a20e409e

http://www.mrexcel.com/forum/excel-...ved-linked-cells-when-data-source-closed.html

http://www.mrexcel.com/forum/excel-questions/510783-troubleshooting-sumproduct-value-error.html

Resolving #VALUE! Errors in Microsoft Excel | AccountingWEB


By the way, I'm using Microsoft Excel 2010 Professional. I'm not an expert on Excel, as I have only used to do some basic calculations, so it's quite possible this problem is easy to solve. There might be an answer to my problem somewhere on the Internet, but I haven't been able to find anything. Please tell me if it's necessary to add anything else to the post.

Thank you very much for your attention and sorry for the inconvenience :)

Happy New Year's Eve/ New Year to everyone on the forums!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
If you are running calcs based on what is in a cell, are you sure that cell contains a real number, and not text that looks like a number?
for that calc =100*A3, what is in A3?
Check that it is a number with =isnumber(A3) FALSE indicates text, not number and we will need to convert it.
Also, check for leading/trailing spaces if it looks like a number.

I have tried setting the cells to number value mode
I think you mean Format here?
Formatting only affects the appearance of a number in a cell, not the actual underlying contents of the cell. If the cell contains a text number, no formatting will change that
 
Upvote 0
Without seeing the sheet, not even knowing what's in A3, it's hard to provide any guidance. See my signature for available methods to share your data (we don't allow workbooks to be uploaded. You can upload to your own site and share a link, but some people can't download files (while at work, for example)).

Are you sure the value in A3 is a number? not a number formatted as text? That would be my first thought.
Or - instead of 56.00, you have 56,00? That was mentioned in one of the articles you posted...
 
Upvote 0
I have tried using the =isnumber(A3) command (=ESNUMERO(A3) in Spanish, as I can't change Excel's language) and the output is false (FALSO in the table). I have recently noticed there is a space after each number on the first column, but when I try to erase it, a decimal number is erased instead. Besides, I can't use points as decimal marks in Excel, as the Spanish version only lets users use commas. This way, if I type a point as a decimal mark, a comma appears on the screen instead.

Sorry for the trouble. It's possible the solution is already in one of the links I have provided, but even though I have tried to follow the steps, I haven't been able to solve the problem. I'm pretty confused at the moment.

By the way, #¡VALOR! is the infamous #VALUE error in Spanish.

Here is the table:

Compression of gases

*ABCDEFG
2Voltage Pression (Volts)Voltage Temperature (Volts)Voltage Volume (Volts)*Pression (kPa)Temperature (K)Height h (cm)
3 0,898 4,883E-3 4,961*#¡VALOR!#¡VALOR!#¡VALOR!
4 0,903 4,883E-3 4,951****
5 0,903 9,766E-3 4,902****
6 0,918 9,766E-3 4,819****
7 0,938 0,015 4,707FALSO***

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:166px;"><col style="width:195px;"><col style="width:156px;"><col style="width:124px;"><col style="width:215px;"><col style="width:189px;"><col style="width:183px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
E3=100*A3
F3=39,5*B3+293,7
G3=1,914*C3+5,869
D7=ESNUMERO(A3)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0
try this

=ESNUMERO(--A3) and see if that gets a result
 
Upvote 0
Does A3 align to the left (text) or right (number) unless you have it formatted
 
Upvote 0
Open a fresh sheet and type in a decimal number. Do the *100 with that cell. Do you still get an error?

If yes:

1. What are your WINDOWS regional settings?
2. You said your using a Spanish version of Excel. Are you sure that's what you installed? You didn't just change the language
3. under the Excel options, on the Advanced tab, is "Use system separators" checked?
 
Upvote 0
Does A3 align to the left (text) or right (number) unless you have it formatted

A3 is aligned to the left, so it's not in number format. However, this doesn't change even if I try to change the format.

Open a fresh sheet and type in a decimal number. Do the *100 with that cell. Do you still get an error?

No, I don't get the error anymore. Nevertheless, if I copy and paste all my data, the error persists.
 
Upvote 0
Right click an empty cell and copy, select Columns A-C, right click, paste special, check the Add radio button, click ok.

What happens if anything?
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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
Back
Top