Template Spreadsheet and Formula

Hello,

New member here that's pulling his hair out trying to figure this out!!

I have a formatted spreadsheet that I'm using as a template. Included in this spreadsheet is a formula in cell A2 (=SUMPRODUCT(G5:G400,J5:J400)). I'm importing data into this spreadsheet from a SQL database using an SSIS package with the data beginning in cell A5

Everything works perfectly EXCEPT the dang formula will not calculate until I open the formula in the formula bar and press Enter.
Now, before everyone tells me to check the Calculation Options and check "Automatic", it already is. I've also ensured that the data in columns G5-G400 and J5-J400 is formatted as numbers, although it doesn't appear to make any difference if the format is number or general.

Can anyone save my sanity!!!


Chuck

Are the contents FORMATTED as numbers or REAL numbers?
If left aligned ( without manual alignment) they are text

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

There is no restriction on posting attachments... only links (a partly-effective anti-spam measure). So. I opened the sample... it's fine. I open the template and paste some stuff in.... and it's fine, too.. Really odd....

Hi Chas,

Maybe not what your want to hear, but the formula in B1 calculates fine for me when I entered some data in G5:G400 and J5:J400

I'm importing data into this spreadsheet from a SQL database using an SSIS package with the data beginning in cell A5

The spreadsheet you posted didn't have any data in it so hard to see what you are actually getting from the SQL database pull.

Just for further testing, use =ISNUMBER(G5) just to make sure these values are really numbers.

Hi Chas,

Yes, I had previously looked at that attachment is it calculates for me just fine. This is indeed odd. I'm not all that familiar with the SSIS package and its data extract, but sounds reasonable.

Again, the only thing I can suggest, if after the data is extracted from the SSIS package, if the data is a number it should calculate. Have you tried to run the formula thru the evaluate?

Evaluate Formula

• Click on the cell with the formula >> Ribbon >> Formulas >> Formula Auditing >> Evaluate Formula

What if you put in N5 and copied down >> =G5*J5.

Does that calculate?

Unfortunately, I am out of suggestions, but I have placed a call in to the other "Guru's"

My guess, SSIS package is treating 1st row as header.
Even if 0 is stored as text in Excel. If you use =ISNUMBER(Cell) it will evaluate as being true.

When data is copied, Excel treats what looks like number as double type and will evaluate correctly.

Take a look in how SSIS package is set up and see if you can address it (or use TextToColumn operation on imported data).

Another alternative is to use Get & Transform to import data from SQL. You will have more fine control over data type and how queries are executed (though this will return data as table with header(s), which may not be desired in this case).

Oh, just had an idea...

What happens if you change formula to below? This should coerce text that's like number to number.
=SUMPRODUCT((G5:G400)*(J5:J400))