Problem using a data connection file - number formatting wont work in formula

[Alphabex]

I am using a data source connection file with columns of simple data. All of the columns calculate correctly with my formula because the decimal is in .25 increments. However, I have one column where the decimal points are not in .25 increments (.30, .60, etc) and that is causing a problem in the computation of the formula. If I change the number to a whole number or .25 decimal point it works fine. I have formatted in every way I can think of, but I have hit a brick wall. I believe it must have something to do with the fast that it is a connection file because if i do the same formula in the data source file, it brings in the number with no problem. Help!

Here it the set up
Format in both the data source connection file and the formula file is a NUMBER, and Decimal Point 2 places.

In the Connection File, the columns are: A = Date, D = Employee Name, G = Point Value (G is the column where the number data is listed)

In the Report (aka Formula File), the formula is =sumifs(G:G,D:D,"Specific Employee Name",A:A,"Specific Date")

Hoping someone can help.
Thank you in advance

[David A Coop]

Hi Alphabex,

Can you post an example - it is difficult to visualise what the issue is!

Regards, David

[FlameRetired]

You're not alone. The question comes up here often.

The formatting won't change the underlying data type. If numbers 'file' as text they will require coercing to numeric values to be summed. I'm guessing you get 0s.

Try selecting column G and apply Data --> Text to Columns. Be sure to select General under Column data format, then Finish. This is usually sufficient to coerce the numeric values.

Please let us know how this works for you. Sometimes other measures are called for.

And yes a file is often helpful.

[FDibbins]

As Dave (not David) pointed out, formatting does not change teh actual cell contents, only the cosmetic view of how we would like to see it. There are 2 kinds of data in excel, numeric and text, and sometimes numbers get pulled in as text, even though they look like a number. Do a quick test of a few of them with =ISNUMBER(cell-ref) if you get a FALSE reply, then you are dealing with a text "number" and you will need to convert it back to a number.

Do the test 1st and we can see what needs to be done to convert

[Alphabex]

Dave...that worked!!! oh thank you so much!!! you have saved me. (sorry for the delay in responding).

[FDibbins]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[FlameRetired]

Glad to hear it. You're welcome, thanks for the feedback and the rep.

Oh, and it's never too late.