PROCESS resizes any input reference into an array/range of any desired size (could be considered as the reverse of LIN/TOROW/TOCOL) with options for output size/style and full control over data type(s) inclusion/exclusion
PROCESS was developed primarily to enable resizing of the input reference...
INDEX.Nth returns (every) nth element(s) (starting at the desired position) from a one-dimensional array and provides output options and full control over data type(s) inclusion/exclusion
(The code is modular (as all my other functions) allowing for easy swapping/optimizing over time. See all...
datatypedatatypes
every nth element
every nth elements
exclude types
if empty
include types
index
nth element
nth element starting at
nth elements
nth elements starting at
replace with
XCOUNT is a powerful cell counting solution to count 'cells in a range' or 'array elements' or rows/columns in ranges/arrays that contain the specified data type (high resolution) and/or meet 'matching criteria'
XCOUNT was designed to count cells in ranges or elements of arrays or the number of...
array element count
cell count
cell count in columns
cell count in range
cell count in rows
count()
counta()
countblank()
datatype
precise datatype
total count excluding
ISXTYPE, companion to XTYPE, is a powerful alternative to Excel's built-in type-based conditional functions such as ISNUMBER, ISTEXT, etc., while packing other useful features as well
ISXTYPE was designed to provide conditional output after assessing the precise and high-resolution data type of...
XTYPE outputs the precise data type of a cell/range or the elements of an array beyond the resolution of Excel's TYPE() or CELL("type",)
XTYPE was designed to provide detailed and high-resolution information about the data type that a cell or cells of a range/array contain. Thus, it is...
I started with a Data Validation Drop Down List in cell N1 that used the column "Symbol" which is the first column in a table named "StockData" in a worksheet named "Stock Data". The column is a Stock data type. It worked great, but there were 81 Stock Issues listed, and I wanted to be able to...
Hello,
I wrote a piece of code that would generate fibonacci sequence, but there is no data type that can hold such big numbers, is there a workaround to make the code contiune to do the math with big numbers?
Thanks.
Option Explicit
Sub Fibonacci()
Dim i As LongPtr, k As LongPtr
i =...
With the intention to help those struggling to find stock names and symbols for Data Types | Stocks, I put in some effort. I have uploaded to OneDrive a file containing all the stocks in the TSX Composite, the Dow Jones Industrial Average, the S&P500 and the NASDAQ100, current as of December 29...
Morning,
If I amend a field format in the source Excel document, will Power BI be able to refresh the data without errors?
Basically I've got a data download from another system and the financial values were stored as text and I need to change them to decimal for a SUM but query editor can't...
I get the below messages in access:
Table is a linked table whose design can't be modified. if you want to add or remove field or change their properties or data types you must do so in the source database.
I want to change the source data types, how do I do that?
Hi. While I've been writing Oracle SQL queries for a few years now, I'm relatively new to MS Access SQL. There are surprisingly few resources available online (via Google) for MS Access SQL. Given that, I'm having difficulty figuring out how to format a field which is text as a number (or vice...
Hi guys,
I'm having this weird issue in my spreadsheet. I have columns populated with fractions but in excel they are not yet selected as fractions (They are "general"). When I select them and change the data type to fraction, it says its a fraction but there is no change. When I individually...
Hi Everyone,
I have been trying to join two Excel tables with a Microsoft Query and kept getting the "Type mismatch in expression" error because my data is different types. I tried using Ctrl + 1 to format the cells (text/ general/ number, etc.) but kept getting the error even if they appeared...
MS Access 2003
Oracle 10g
Windows 7
I am basically running data updates every night into local MS Access tables from source data tables in Oracle.
This worked fine, until some change on the network which produced a timeout error for all append queries that used pass-through queries.
In order...
Hi all,
I'm trying to come up with a simple way to explain to new users in what situations you would use excel's various lookup formulas, to show why it's important to know what format (cross-sectional vs. panel) your datasets are in -- for example, VLOOKUP can be used to pull values from a...
I have a complicated routine that I am writing that would normally be Boolean but there are a few times when the answer will be To Be Determined. There are some that are clearly defined with a lot of IF THEN ELSE statements but when all is said and done, there will be three answers.
There are a...
Hello all,
I'm using the following code to enter a formula into column B which references values in column T:
'Populate Program name column with formulas now referencing values in column-T.
Dim ab As Long, c As Long
ab = Cells(Rows.Count, "T").End(xlUp).Row
For c = 2 To ab
Range("B" &...
I'm new to using PowerPivot but very proficient in Pivot Tables. After I import data from a CSV file into PowerPivot (regardless if data was recognized on import as various types or as all text) I then, column by column, set the Data Types to Decimal, Whole Numbers, Dates, etc as needed and I...
I am collecting data through an ActiveXcontrol. The command I am using is 'ai = DataqSdk1.GetData'. This data is coming in as type 'variant'. I want to be able to manipulate this data (from counts to a pressure value), but I first need to convert its data type to double. I tried the following...
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.