Excel imported data #value error

up vote
0
down vote

favorite

I am trying to manipulate data imported to Excel by websites.

I imported data from:
https://finance.yahoo.com/q/hp?a=&b=&c=&d=8&e=5&f=2015&g=d&s=msft&ql=1
to Excel, using the "Get data from Web" function.

However, I get an "#VALUE" error everytime I calcule using the imported cells.

In the table, all the data is imported from the Yahoo Finance website:
In this example, I tried to calcule "=G7*E7"

Why do I always get an error message, while trying to use these numbers? I even tried to use =VALUE, but I get 0 as the answer, always.

asked Sep 6 '15 at 0:04

m3dl

1225

1

What cells in row 7 are you trying to calculate, and what function are you using?
– BillDOe
Sep 6 '15 at 0:10

@BillOer In the picture, G7*E7. But any operation with any of the cells returns me an error message :(
– m3dl
Sep 6 '15 at 0:12

1

Okay, my bad! But what good is (essentially) squaring the Close? What am I missing? And I do not get the #VALUE error doing the same thing.
– BillDOe
Sep 6 '15 at 0:14

1

According to Excel's help, you can get this error if you import external data and the external source is unavailable.
– BillDOe
Sep 6 '15 at 0:34

1

@weber, when I download your spreadsheet and edit in my machine, I cannot duplicate your error. I get the desired result.
– BillDOe
Sep 6 '15 at 1:20

|
show 5 more comments

up vote
0
down vote

favorite

I am trying to manipulate data imported to Excel by websites.

I imported data from:
https://finance.yahoo.com/q/hp?a=&b=&c=&d=8&e=5&f=2015&g=d&s=msft&ql=1
to Excel, using the "Get data from Web" function.

However, I get an "#VALUE" error everytime I calcule using the imported cells.

In the table, all the data is imported from the Yahoo Finance website:
In this example, I tried to calcule "=G7*E7"

Why do I always get an error message, while trying to use these numbers? I even tried to use =VALUE, but I get 0 as the answer, always.

asked Sep 6 '15 at 0:04

m3dl

1225

1

What cells in row 7 are you trying to calculate, and what function are you using?
– BillDOe
Sep 6 '15 at 0:10

@BillOer In the picture, G7*E7. But any operation with any of the cells returns me an error message :(
– m3dl
Sep 6 '15 at 0:12

1

Okay, my bad! But what good is (essentially) squaring the Close? What am I missing? And I do not get the #VALUE error doing the same thing.
– BillDOe
Sep 6 '15 at 0:14

1

According to Excel's help, you can get this error if you import external data and the external source is unavailable.
– BillDOe
Sep 6 '15 at 0:34

1

@weber, when I download your spreadsheet and edit in my machine, I cannot duplicate your error. I get the desired result.
– BillDOe
Sep 6 '15 at 1:20

|
show 5 more comments

up vote
0
down vote

favorite

I am trying to manipulate data imported to Excel by websites.

I imported data from:
https://finance.yahoo.com/q/hp?a=&b=&c=&d=8&e=5&f=2015&g=d&s=msft&ql=1
to Excel, using the "Get data from Web" function.

However, I get an "#VALUE" error everytime I calcule using the imported cells.

In the table, all the data is imported from the Yahoo Finance website:
In this example, I tried to calcule "=G7*E7"

Why do I always get an error message, while trying to use these numbers? I even tried to use =VALUE, but I get 0 as the answer, always.

asked Sep 6 '15 at 0:04

m3dl

1225

I am trying to manipulate data imported to Excel by websites.

I imported data from:
https://finance.yahoo.com/q/hp?a=&b=&c=&d=8&e=5&f=2015&g=d&s=msft&ql=1
to Excel, using the "Get data from Web" function.

However, I get an "#VALUE" error everytime I calcule using the imported cells.

In the table, all the data is imported from the Yahoo Finance website:
In this example, I tried to calcule "=G7*E7"

Why do I always get an error message, while trying to use these numbers? I even tried to use =VALUE, but I get 0 as the answer, always.

microsoft-excel microsoft-excel-2013 import

asked Sep 6 '15 at 0:04

m3dl

1225

asked Sep 6 '15 at 0:04

m3dl

1225

asked Sep 6 '15 at 0:04

m3dl

1225

asked Sep 6 '15 at 0:04

m3dl

1225

asked Sep 6 '15 at 0:04

m3dl

1225

1

What cells in row 7 are you trying to calculate, and what function are you using?
– BillDOe
Sep 6 '15 at 0:10

@BillOer In the picture, G7*E7. But any operation with any of the cells returns me an error message :(
– m3dl
Sep 6 '15 at 0:12

1

Okay, my bad! But what good is (essentially) squaring the Close? What am I missing? And I do not get the #VALUE error doing the same thing.
– BillDOe
Sep 6 '15 at 0:14

1

According to Excel's help, you can get this error if you import external data and the external source is unavailable.
– BillDOe
Sep 6 '15 at 0:34

1

@weber, when I download your spreadsheet and edit in my machine, I cannot duplicate your error. I get the desired result.
– BillDOe
Sep 6 '15 at 1:20

|
show 5 more comments

1

What cells in row 7 are you trying to calculate, and what function are you using?
– BillDOe
Sep 6 '15 at 0:10

@BillOer In the picture, G7*E7. But any operation with any of the cells returns me an error message :(
– m3dl
Sep 6 '15 at 0:12

1

Okay, my bad! But what good is (essentially) squaring the Close? What am I missing? And I do not get the #VALUE error doing the same thing.
– BillDOe
Sep 6 '15 at 0:14

1

According to Excel's help, you can get this error if you import external data and the external source is unavailable.
– BillDOe
Sep 6 '15 at 0:34

1

@weber, when I download your spreadsheet and edit in my machine, I cannot duplicate your error. I get the desired result.
– BillDOe
Sep 6 '15 at 1:20

What cells in row 7 are you trying to calculate, and what function are you using?
– BillDOe
Sep 6 '15 at 0:10

@BillOer In the picture, G7*E7. But any operation with any of the cells returns me an error message :(
– m3dl
Sep 6 '15 at 0:12

Okay, my bad! But what good is (essentially) squaring the Close? What am I missing? And I do not get the #VALUE error doing the same thing.
– BillDOe
Sep 6 '15 at 0:14

According to Excel's help, you can get this error if you import external data and the external source is unavailable.
– BillDOe
Sep 6 '15 at 0:34

@weber, when I download your spreadsheet and edit in my machine, I cannot duplicate your error. I get the desired result.
– BillDOe
Sep 6 '15 at 1:20

|
show 5 more comments

2 Answers
2

active

oldest

votes

up vote
2
down vote

accepted

The problem is Excel is importing the data as Text.

You will have to do the following things:

Format the cells to numbers

If necessary change . to , depending on your location/settings

All data has to be either manually re-enter (Step into the Cell and press Enter)

Or you will have to change the following settings in Excel:

In Options -> Advanced -> Data delimiter:

Change the Decimal from , to .

answered Sep 6 '15 at 1:20

DragonSamu

1386

Thank you! The problem was wrong decimal separator. After changed, the errors disappeared.
– m3dl
Sep 6 '15 at 1:30

1

That explains why I couldn't duplicate @weber's error.
– BillDOe
Sep 6 '15 at 1:43

add a comment |

up vote
1
down vote

Additional info; converting TEXT values to NUMERIC:

Type a single 1 (the digit, one) into a free cell.

Select that cell, press CTRL and hit c (i.e. copy the content)

Select the cells you wish to convert

Choose "Paste Special" (where it is depends on Excel version)

Make sure to select "Multiply" from the options in the dialog that
opened.

Click OK or press Enter

You may also " Add '0' " to achieve the same effect

answered Sep 6 '15 at 8:06

Hannu

3,9221925

add a comment |

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f968904%2fexcel-imported-data-value-error%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

up vote
2
down vote

accepted

The problem is Excel is importing the data as Text.

You will have to do the following things:

Format the cells to numbers

If necessary change . to , depending on your location/settings

All data has to be either manually re-enter (Step into the Cell and press Enter)

Or you will have to change the following settings in Excel:

In Options -> Advanced -> Data delimiter:

Change the Decimal from , to .

answered Sep 6 '15 at 1:20

DragonSamu

1386

Thank you! The problem was wrong decimal separator. After changed, the errors disappeared.
– m3dl
Sep 6 '15 at 1:30

1

That explains why I couldn't duplicate @weber's error.
– BillDOe
Sep 6 '15 at 1:43

add a comment |

up vote
2
down vote

accepted

The problem is Excel is importing the data as Text.

You will have to do the following things:

Format the cells to numbers

If necessary change . to , depending on your location/settings

All data has to be either manually re-enter (Step into the Cell and press Enter)

Or you will have to change the following settings in Excel:

In Options -> Advanced -> Data delimiter:

Change the Decimal from , to .

answered Sep 6 '15 at 1:20

DragonSamu

1386

Thank you! The problem was wrong decimal separator. After changed, the errors disappeared.
– m3dl
Sep 6 '15 at 1:30

1

That explains why I couldn't duplicate @weber's error.
– BillDOe
Sep 6 '15 at 1:43

add a comment |

up vote
2
down vote

accepted

The problem is Excel is importing the data as Text.

You will have to do the following things:

Format the cells to numbers

If necessary change . to , depending on your location/settings

All data has to be either manually re-enter (Step into the Cell and press Enter)

Or you will have to change the following settings in Excel:

In Options -> Advanced -> Data delimiter:

Change the Decimal from , to .

answered Sep 6 '15 at 1:20

DragonSamu

1386

The problem is Excel is importing the data as Text.

You will have to do the following things:

Format the cells to numbers

If necessary change . to , depending on your location/settings

All data has to be either manually re-enter (Step into the Cell and press Enter)

Or you will have to change the following settings in Excel:

In Options -> Advanced -> Data delimiter:

Change the Decimal from , to .

answered Sep 6 '15 at 1:20

DragonSamu

1386

answered Sep 6 '15 at 1:20

DragonSamu

1386

answered Sep 6 '15 at 1:20

DragonSamu

1386

answered Sep 6 '15 at 1:20

DragonSamu

1386

Thank you! The problem was wrong decimal separator. After changed, the errors disappeared.
– m3dl
Sep 6 '15 at 1:30

1

That explains why I couldn't duplicate @weber's error.
– BillDOe
Sep 6 '15 at 1:43

add a comment |

Thank you! The problem was wrong decimal separator. After changed, the errors disappeared.
– m3dl
Sep 6 '15 at 1:30

1

That explains why I couldn't duplicate @weber's error.
– BillDOe
Sep 6 '15 at 1:43

Thank you! The problem was wrong decimal separator. After changed, the errors disappeared.
– m3dl
Sep 6 '15 at 1:30

That explains why I couldn't duplicate @weber's error.
– BillDOe
Sep 6 '15 at 1:43

add a comment |

up vote
1
down vote

Additional info; converting TEXT values to NUMERIC:

Type a single 1 (the digit, one) into a free cell.

Select that cell, press CTRL and hit c (i.e. copy the content)

Select the cells you wish to convert

Choose "Paste Special" (where it is depends on Excel version)

Make sure to select "Multiply" from the options in the dialog that
opened.

Click OK or press Enter

You may also " Add '0' " to achieve the same effect

answered Sep 6 '15 at 8:06

Hannu

3,9221925

add a comment |

up vote
1
down vote

Additional info; converting TEXT values to NUMERIC:

Type a single 1 (the digit, one) into a free cell.

Select that cell, press CTRL and hit c (i.e. copy the content)

Select the cells you wish to convert

Choose "Paste Special" (where it is depends on Excel version)

Make sure to select "Multiply" from the options in the dialog that
opened.

Click OK or press Enter

You may also " Add '0' " to achieve the same effect

answered Sep 6 '15 at 8:06

Hannu

3,9221925

add a comment |

up vote
1
down vote

Additional info; converting TEXT values to NUMERIC:

Type a single 1 (the digit, one) into a free cell.

Select that cell, press CTRL and hit c (i.e. copy the content)

Select the cells you wish to convert

Choose "Paste Special" (where it is depends on Excel version)

Make sure to select "Multiply" from the options in the dialog that
opened.

Click OK or press Enter

You may also " Add '0' " to achieve the same effect

answered Sep 6 '15 at 8:06

Hannu

3,9221925

Additional info; converting TEXT values to NUMERIC:

Type a single 1 (the digit, one) into a free cell.

Select that cell, press CTRL and hit c (i.e. copy the content)

Select the cells you wish to convert

Choose "Paste Special" (where it is depends on Excel version)

Make sure to select "Multiply" from the options in the dialog that
opened.

Click OK or press Enter

You may also " Add '0' " to achieve the same effect

answered Sep 6 '15 at 8:06

Hannu

3,9221925

answered Sep 6 '15 at 8:06

Hannu

3,9221925

answered Sep 6 '15 at 8:06

Hannu

3,9221925

answered Sep 6 '15 at 8:06

Hannu

3,9221925

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Super User!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Bdtyktl