Details: Written by Super User; Category: (Un)Perplexed Spready; Published: 23 February 2025

Introduction to (Un)Perplexed Spready

Purpose of the (Un)Perplexed Spready software is to embed power of Artificial Intelligence (AI) LLM (Large Language Models) into spreadsheet formulas. By utilizing AI power, (Un)Perplexed Spready becomes a spreadsheet software that thinks! It combines familiar spreadsheet features with revolutionary AI models for effortless data analysis and smart insights, bringing you AI-powered formulas to unlock insights beyond the reach of Excel or Google Sheets.

You can read more about (Un)Perplexed Spready software in this introductory article: Introduction to (Un)Perplexed Spready

Here we will focus more on practical things, how can you setup the software and engage advanced AI models to do useful work in your spreadsheets.

Unleash Artificial Intelligence in your spreadsheets! Drink you coffee, while the AI is doing hard job for you!

License Activation

While regular spreadsheet software features are completely free, you will need to purchase and activate commercial license in order to be able to harness AI-driven power in your formulas. During the ordering process, you will be required to provide your Machine Code number. This is an automatically generated unique designation of your computer. By using this machine code identifier, we will generate personalized unique license key for you.

Settings

You will notice "Settings"menu, with various settings options.

General Settings

Number Format Settings

Under "Number Format Settings" there is a form with multiple tabs: "Number", "Currency" and "Date/time". Use these tabs to setup corresponding parameters,

CSV parameters

"CSV parameters" form also contains multiple tabs with various parameters concerning how exported or imported csv files will be formatted and interpreted.

Notice that AI-driven calculation is slow and thus you will need to pay attention and take care of saving calculated results as CSV exports. Thus, exporting to csv is very important feature of (Un)Perplexed Spready and you will use it a lot to save calculated results.

Currency symbols

Under "Currency symbols" you can maintain list of currency symbols.

Various flags

Available flags, which you can switch-on and off, are self-explanatory. Available options are: "Read formulas", "Update row heights after loading", "Automatically detect cell type".

Settings Related to AI features

Notice separate grouop of settings in the Setting menu:

Also available in the toolbar:

These settings are related to consumation of advanced AI LLLM models.

Perplexity AI API Settings

Perplexity AI (https://www.perplexity.ai/) is a major provider of high quality AI LLLM models. By utilizing (Un)Perplexed Spready, you can now utilize it do useful work for you! Are you excited? You should be!

In order to be able to use power of Perplexity API, you will need to become PRO subscriber of the Perplexity AI and have commercial API key. Perplexity API is consumed via the following end-point: https://api.perplexity.ai/chat/completions

API delay setting (in milliseconds) is very important parameter, because it determines speed of calculation of AI-driven formula. Notice, however, that this value cannot be an arbitrary value, but must be aligned with your subscription tier, i.e. what is your current limit enforced by Perplexity. At the moment of writing, Perplexity AI rate limits were as described below.

    Perplexity API rate limits are based on usage tiers determined by cumulative credit purchases:

        Tier 0 (default): 50 requests per minute
             API_DELAY_MS = (60 seconds * 1000 ms/second) / 50 requests = 1200 ms

Tier 1 ($50 spent): 150 requests per minute
API_DELAY_MS = (60 seconds * 1000 ms/second) / 150 requests = 400 ms

Tier 2 ($250 spent): 500 requests per minute
API_DELAY_MS = (60 seconds * 1000 ms/second) / 500 requests = 120 ms

Tier 3 ($500 spent): 1000 requests per minute
API_DELAY_MS = (60 seconds * 1000 ms/second) / 1000 requests = 60 ms

I recommend you filling out the API access form at https://perplexity.typeform.com/apiaccessform to get early access to new features and potentially discuss higher usage limits for your specific needs.

Local AI API Settings

As an alternative to commercial Perplexity AI, you can either use our remote server, with Mistral 7b model running, or install Ollama and run available models on your own computer.

Here you can setup settings for your locally installed AI model.

How to use Ollama

You will be shocked and amazed how easy is to install and run advanced AI models on your own computer, by using Ollama platform!

First download and install Ollama platform: https://ollama.com/

Then search for a model you will download and install. There are multitude of models available and you will need to choose a model suitable for your purposes and hardware limitations: https://ollama.com/search

If you have a low spec computer, but still want to run a decent model, I can recommend you Mistral model. You can run it with a simple command line prompt: ollama run mistral:7b

Defer Formula Calculation

Deferring of AI-driven formulas calculation is an important concept in (Un)Perplexed Spready. We are all accustomed to get formula result instantly in a spreadsheet. However, artificial intelligence calculation requires much resources and is much slower than regular spreadsheet formula calculation. Speed, off course, is dependent on your HW specifications (in case of locally run AI model) or your rate limits (in case of running Perplexity AI). Therefore, especially if you have a big document with many cells having AI-drive formulas, it would be unwise to calculate such cells instantly. This would make your spreadsheet appear to be frozen, during long-running calculation. For that reason, in (Un)Perplexed Spready, AI-driven formula calculation is deferred by default, and you will need to trigger calculation explicitly, by corresponding commands (explained later below).

You can control that behavior by switching on or off the corresponding setting flag "Defer Formula Calculation".

AI-driven Spreadsheet Functions

At the heart of (Un)Perplexed Spready is its innovative support for custom AI-driven functions that can do useful work on your spreadsheet data.

Currently, in the program there are available 3 groups of such functions:

"PERPLEXITY ()" group of functions: Call top-tier commercial AI directly (via Perplexity AI’s API)
"ASK_LOCAL ()" group of functions: Query locally installed AI models through the Ollama platform
"ASK_REMOTE ()" group of functions: Access remotely hosted AI model for on-the-fly analysis

With these functions, you can simply enter a formula like:
=PERPLEXITY1(A2, "What is the category of this product? Choose between following categories: meat, fruits, vegetables, bakery, dairy, others.")
or

=ASK_LOCAL1(A2, "From product description extract the product measure (mass, volume, size etc.) and express it in S.I. units")

=ASK_REMOTE1(A2, "What is aggregate phase of the product (solid, liquid, gas)?")

or
=ASK_LOCAL2(A2, B2, "Are two products having same color?")

=ASK_LOCAL2(A2, B2, "In what kind of packaging container is the product packed? Example of containers are: box, bottle, pouch, can, canister, bag, etc.")

=PERPLEXITY2(A2, B2, "What are common characteristics of the two products?")

=PERPLEXITY(A2, B2, C2, "How can we achieve Input1 from Input2, by utilizing Input3?")

=ASK_LOCAL3(C2, D2, E2,"Classify Input1 and Input2 by Input3.")

— and see powerful AI-generated output appear instantly in your spreadsheet!

As you probably figured out so far, these three group of functions are conceptually the same, different names just indicate that they utilize different source of AI model. "PERPLEXITY" functions utilize remote commercial Perplexity AI model via API, "ASK_LOCAL" functions use locally installed AI model (via Ollama platform), while "ASK_REMOTE" calls free AI model installed on our server.

So, important to understand is that there is no conceptual difference between those three groups of functions. All three of them, however, come in three variants, differing in number of input parameters, or better said, in number of input cell ranges which are passed to AI input prompt (i.e. input query).

Notice: When you have multiple input cell ranges as parameters of the function, you can refer to them in the textual instructions (i.e. AI prompt) by "Input1", "Input2" and "Input3". The AI model will understand that you are referring to first, second and the third input cell range value, respectively.

AI-driven Spreadsheet Functions with 1 Parameter

These functions are :
=PERPLEXITY1 (A2, "Some instruction to AI...")

=ASK_LOCAL1 (A2, "Some instruction to AI...")

=ASK_REMOTE1 (A2, "Some instruction to AI...")

An example of a formula with such function with one input cells range: =PERPLEXITY1(B7,"What is the size, volume, mass or any other measure of the product, expressed in SI units? If none then N/A")

Another example, extracting unique product categories from a range of listed products: =PERPLEXITY1(B$2:B$50,"Extract unique product categories from the list of products and output them as comma-separated list of values.")

AI-driven Spreadsheet Functions with 2 Parameters

These functions are :
=PERPLEXITY2 (A2, B2, "Some instruction to AI...")

=ASK_LOCAL2 (A2, B2, "Some instruction to AI...")

=ASK_REMOTE2 (A2, B2, "Some instruction to AI...")

An example of formula with such function with 2 input cell ranges: =ASK_LOCAL2(B2,B$2:B$50,"For the product under Input1 find the most similar product from the list of products under Input2, but exclude the value of the Input1 itself.")

Are you amazed? Do you already see the potential? Have you noticed that I used whole range of cells in the second parameter? Do you feel the power?

Notice, though, that quality of results, as well as verbosity, greatly depend on the AI model used. With some other model, for example, it might look much more verbose. For example:

When that happens, you can try to enforce the AI model to be more concise instead of being verbose, by providing additional instruction in the formula. For example, I did something like this, to force Perplexity's DeepSeek R1 model (r1-1776) to be more concise: =PERPLEXITY2(B16,B$2:B$50,"From the list of products listed in the Input2, find a product which is the most similar to the product in the Input1, but excluding the Input1 itself! Write brief and concise answer, with no comments, no reasoning and no extra text, just concise final result!")

AI-driven Spreadsheet Functions with 3 Parameters

These functions are :
=PERPLEXITY3 (A2, B2, C2, "Some instruction to AI...")

=ASK_LOCAL3 (A2, B2, C2, "Some instruction to AI...")

=ASK_REMOTE3 (A2, B2, C2, "Some instruction to AI...")

As an example of formula let's use the formula from previous example, but let's additionally constraint it with product container type. Our example of such function with 3 input cell ranges: =ASK_LOCAL3(B12,B$2:B$50,D12,"For the product under Input1 find the most similar product from the list of products under Input2, but exclude the value of the Input1 itself. Two products must be packed in the same type of packaging container, specified under Input3.")

I suppose this was not the best way to illustrate business case where you could use 3 input ranges as parameters, but I hope you got the point - you can utilize three different cell ranges in your AI query, referring to them as "Input1", "Input2", "Input3" and do some complex analysis with them.

Advanced Business Scenarios

Only your imagination is limit when considering possible use cases. By using Perplexity AI, you can facilitate on-line search capabilities and embed it inside your spreadsheet formula.

Look this advanced formula as an example:

=PERPLEXITY3(A2,C2,D2,"By using A breakdown of the 12 award categories being defined on the web page: https://awards.retailgazette.co.uk/award-categories/ (namely: 'Best Retailer Over £500m', 'Best Retailer Under £500m', 'Own Brand Game Changer', 'Ecommerce Game Changer', 'Marketing Game Changer', 'Sustainability Game Changer', 'Workplace Game Changer', 'Game Changing Team', 'Grocery Game Changer', 'Fashion Game Changer', 'New Store Game Changer', 'Supply Chain Game Changer') label the company provided in the Input1 with with the category (only 1 of the 12 categories) they’re most likely to sponsor, based on the product/service the company offers. In order to determine the company product/service offer, investigate company web page provided as Input2 and Linkedin company page being provided as Input3. Then compare the product/service the company offers and determine the most suitable award category as final result.")

Result is amazing:

Similarly, we can find industry domains for list of companies, by entering following formula:

=PERPLEXITY3(A2,C2,D2,"For the company provided as Input1 determine the product/service offered, by investigating company web page provided as Input2 and Linkedin company page being provided as Input3. If needed, search Internet for additional information to determine exact industry the company operates within. Return the label of industry in which the company operates within, as final result.")

Executing AI-driven functions

As already explained, deferring of AI-driven formulas calculation is deliberately enforced in (Un)Perplexed Spready, by default. You can switch on or off that behavior by corresponding setting "Defer Formula Calculation". The reason is the fact that AI-driven formulas require much more resources and time to execute than regular spreadsheet formulas. Therefore, especially if you have a big document with many cells having AI-drive formulas, it would be unwise to calculate such cells instantly. This would make your spreadsheet appear to be frozen, during long-running calculation. For that reason, in (Un)Perplexed Spready, AI-driven formula calculation is deferred by default, and you will need to trigger calculation explicitly, by corresponding commands placed under Worksheet menu.

Those commands are also present in the toolbar.

Command "Recalculate Formulas" will trigger calculation of the whole spreadsheet. If there are many cells with AI-driven formulas, depending on the AI model used, your HW specs or Perplexity rate limits (and consequent API calls delay) this might take significant time. Use this option when you are sure that formulas are correctly set.

Command "Recalculate Selected Cell" triggers calculation of only the selected cell. Use it to check whether your formula works correctly.

Command "Recalculate Selected Range" is similar to the previous one, but triggers calculation for multiple selected cells.

Custom Copy/Paste for Deferred AI-driven Functions

As any spreadsheet software, our program has regular copy/paste actions. But, besides those, (Un)Perplexed Spready introduces additional custom copy/paste actions specifically for copy/pasting AI-driven formulas from one cell range into another cell range. If you use regular copy/paste, only calculated value will be copied. If you wish to copy/paste formula, then you must use custom copy/paste commands.

Custom Copy (CTRL+Alt+C) and Custom Paste (CTRL+Alt+V) are available in the "Edit" menu and also in the toolbar.

Supported Regular Functions

(Un)Perplexed Spready is based on Lazarus/CodeTyphon/FreePascal fpspreadsheet component, inherited most features from it.

The list of inherited regular functions available:https://wiki.lazarus.freepascal.org/FPSpreadsheet:_List_of_formulas

Inspector

Under the "View" menu you will find option to show so-called "Inspector".

If this option is switched-on then on the right side there will be shown Inspector, with multiple tabs.

Using (Un)Perplexed Spready