Today I want to demonstrate a user defined function that downloads data from ThingSpeak to Excel so you can easily analyze and chart the data yourself, in excel.
The way this works is that using a simple http address with your channel and field number and parameters, you can easily fetch values from your feed.
I got a question about counting background colors in a cell range. Excel uses two different properties to color cells and they are ColorIndex and Color property.
The ColorIndex property has 56 different colors, shown below.
The color property holds up to 16 777 216 colors. I tried to color 16 columns with 1048576 rows each (16 * 1048576 = 16 777 216) using the color property but excel returned this error after 65277 cells.
What can an excel defined table do for you? It will simplify your work with data sets, adding or removing data, filtering, sorting, readability using cell formatting, cell references, formulas and more. I will go through all this in greater detail, keep reading.
Table of Contents
- Create table
- Name a table
- Edit data
- Sort data
- Filter data
- Structured references
- Formulas in a table
- Show totals
- Named ranges
- Data Validation List
- Working with a filtered excel table
- Quickly navigate to a table
Hi Oscar,Your formula works great, however, I was wondering if there is capability to add another countif criteria so that it produces a random unique number different from the one above AS WELL AS the one to the left.
I have 7 groups, 7 tables, and 7 rotations for which each group will move to a different table.
With your formula, I am able to successfully assign each group a random order of the 7 tables to rotate too with no repeats (however I am unable to guarantee that for each rotation only one group is at each table), OR I am able to produce a schedule that has max one group per table per rotation, but then I am not able to have each group have no repeats for the tables they are rotating too.
Is there a way to tweak the formula to satisfy both requirements where each group has a random 1-7 table rotation, AND for each rotation there is only one group per table? Thanks!! Appreciate the help.
This is not as easy as it may seem. I first tried using the same technique as in the post but the formula returned errors which is really not surprising if you think of it. I will explain why later in this post but first you need to know how the original formula works.