Average error manipulating values in a particular csv file

Albertu95

New Member
Joined
Oct 18, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hello to all,
I'm trying to manipulate certain data coming from an import from a csv file (that I wanted to upload, but it's not allowed from the forum) with numerical values.
The operation that I want to do is the average of the columns but, when I select the values, I get div/0! error
I tried to format the columns as numbers, but nothing changes. If I try to do the sum of 2 values, this works correctly.
It has been few days that I'm trying to solve this problem.

Can someone help me please?

CSV look:

1666104915964.png


imported data and error:

1666105543408.png


Version of office: office365
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Welcome to the Board!

What is the exact formula in cell B149 that is returning the error?
How exactly are you opening this CSV file in Excel?

Though you cannot upload files to this sight, you can post snippets of your workbook that allow us to see your formulas and easily copy and paste your data.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
It was a formatting problem, I solved it selecting all the range of the data and formatting it to number in this way:

VBA Code:
  [B16:G143].Select 'number conversion csv values
   With Selection
   .NumberFormat = "General"
   .Value = .Value
   End With 'end number conversion csv values
 
Upvote 0
Excellent!

Note that you do not need to select the range in order to work with it (and it usually speeds up your code to remove "Select" statements)
You can simply do this:
VBA Code:
   With Range("B16:G143") 'number conversion csv values
       .NumberFormat = "General"
       .Value = .Value
   End With 'end number conversion csv values
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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