Calculated Column in Excel List | CLEARIFY

With QQube Addin... I have set up calculated columns on my Excel list and they always are "text" formatted... which means any formulas will not be recognized unless you change the format of the column to something other than 'Text'...annoying to say the least. In my experience, this is not normal Excel functionality..   What is causing this behavior in QQube and and can it be changed?

Thanks,

Fran

First let's review what we do with QQube fields:

All measures in QQube e.g. quantities, amounts, rates are formatted explicitly in QQube as numeric with the appropriate decimal places.   If you place one of those in the Values area for a pivot table, they will be recognized as such, e.g. sum

If you perform calculated fields in a pivot table based upon only those fields, they will show as numeric.

If there are text fields - and as a note remember that ALL custom fields are text fields - Excel will always recognize calculations on these fields as text, whether you have numbers in them or not. This is true for pivot or list tables in Excel. (View this article on how QQube handles custom fields)

If you are referring to calculated fields for lists, then yes Excel defaults to the 'general' format, and you need to change the format of the column.  This is just Excel, as we will explain below.

Additionally Dynamic Lists - from any database - reflect a 'General' default format, whether it is a number or not.  This is slightly different from the Pivot Table behavior as described above.

KB 922069 from Microsoft, explains the following:

By default, all worksheet cells are formatted with the General number format. With the General number format, anything that you type in the cell is usually left as-is. For example, if you type 36526 in a cell and then press ENTER, the cell contents are displayed as 36526

It goes further on:

Typically, Excel 2007 applies automatic number formatting whenever you type the following types of data in a cell:

• Currency
• Percentage
• Date
• Time
• Fraction
• Scientific

So even if you type in say the value '1' or 1.123 - Excel still recognizes this as a 'General' Format, and not numeric.

Lastly, there is no way to change this behavior, unless you create an Excel template file. 

We appreciate your frustration with this, as we did when we first noticed this behavior.

If you can please upload two spreadsheets:  one with the columns 'before' and one with the columns 'after you have created the calculated field' example please?   This would give us a chance to review this specific behavior that you are seeing.

Thank you.

when I add a custom field, In the first cell under the header, I always immediately select, "Clear All"  under the "Home" tab, Editing, Clear button. As formulas are entered into the first cell and are copied down automatically by excel's table feature,  this solves the problem for me.