Using formulas with imported

**mo0ki** · 08-02-2012, 09:38 PM

Hi Everybody,

Basically i'm maintaining a spreadsheet which has a data connection to an SQL database. It imports customer submitted ratings for our employees. There are 4 columns for each employee each with a value out of 10. My problem is that no matter what i do i cannot format these columns as numbers, which is absolutley necessary as averages and such need to be found.

In the format cells dialog box it states they are all numbers, but when I try and create average formulas i get a div#0 error no matter what. Also, the numbers are aligned left which leads me to believe their format has not been changed.

Would anyone be able to help with this? It would be greatly appreciated as I'm relativley new to Excel.

Thanks

**Trebor76** · 08-02-2012, 10:08 PM

Hi mo0ki,

Welcome to the forum!!

Try incorporating the following code into your existing macro or run it as a stand alone solution:

Please Login or Register  to view this content.

Regards,

Robert

**mo0ki** · 08-02-2012, 11:18 PM

Hi Robert,

Thanks alot for the welcome and your reply. Unfortunatley I am very new to excel and am not exactly sure where to implement this.

The data connection was already set up on the spread sheet when I started working on it. I've just thought of some ways to gather meaningful information from the raw data in the spreadsheet. Would I implement that code in the 'command text' portion of the 'Definition' tab in 'Connection Properties'? Thats where the current code is which retrieves the data from the database.

Thanks for your help!

**Trebor76** · 08-03-2012, 12:12 AM

OK, maybe it's better to do it the manual way via these five steps:

1. Make sure you're on the tab in question and in any empty cell enter an 1 and then copy this to the clipboard (Ctrl + C)
2. Highlight the relevant range and from the shortcut menu (accessed via a right mouse click) select Paste Special
3. From the Paste Special dialog click the Multiply option (radio button) and click OK
4. Press Esc
5. Clear the contents of the cell you used in step 1.

HTH

Robert