Tools Used

1. Microsoft Co-pilot (Pro / Microsoft 365)
2. Microsoft Excel
3. Notepad (in-built in Windows)
4. Snipping Tool (in-built in Windows)

Problem Statement

You have an Excel file containing Salary figures of our employees for individual month. As a payroll in-charge you need to calculate Profession Tax for each employee for every month. Below is the screenshot of the same for reference Purpose

Date column contains 1^st day of month to identify the month to which Salary figure pertains. Profession Tax calculation varies from state to state. Let’s assume that you want to calculate Profession Tax for “Maharashtra” state, where the slabs are as below

We will use Microsoft Co-pilot chat-based interface to explain requirements and generate complex Excel formula to calculate the same.

Solution

One can quickly get a solution by uploading Excel file on AI platform. But it will lead to breach of data privacy. So, we will use blunt prompts to describe our requirement and explain our data, by exposing just a few sample rows to LLM. Below are the steps for the solution for the problem statement.

Step #1: Identify & Hide PII

Maintaining data privacy is the first and foremost duty of any user using an AI platform. One must first identify any PII (Personally Identifiable Information) or any sensitive business information which might lead to breach of privacy. In our case study, “Name” column / field is a PII. So, we need to make a copy of this worksheet & exclude it. Just insert one more worksheet & copy-paste all the data (excluding Name column). Refer below screenshot.

Step #2: Describe the data

An AI platform needs to know some details about the data, since it attempts to analyze patterns from it. Assuming you have paid version of Co-pilot subscription (which is add-on to Microsoft 365) open website copilot.microsoft.com . A window as below should appear.

Tweak conversation style to Precise, since we intend to generate formula for mathematical calculation, which needs to be accurate.

Simultaneously, copy-paste few rows of data from Excel worksheet, and paste it into Notepad (refer below screenshot)

Now in co-pilot prompt window, write a prompt as below

I have some data in Excel. First row contains header followed by data rows, as below

Use Shift + Enter key to jump to next line. Select and copy data from Notepad and paste it below.

Use below screenshot for reference.

On pressing enter, Co-pilot will start analyzing the data, and might produce a response like this.

Note: Responses in an AI platform keep on varying each time you enter the same prompt.

Step #3: Explain logical conditions to Co-pilot

Co-pilot internally uses OpenAI organization Chat-GPT LLM (Large Language Model), which understands multi-model data. It can understand Natural Language prompts and even data or conditions respected graphically in image files. Use Snipping Tools of windows to capture image of the conditions described in tabular form (use Windows + Shift + S to activate it and draw rectangle to capture as image). Save As screen capture to image file. Below is tabular image of conditions captured from PPT

Step#4: Refining Generated Formula

Co-pilot might not generate fool-proof formula in single shot. Further prompts are required in such a scenario asking it to refine the formula. Like, in the above response, it generated separate formula for Females & Males. We will ask it to merge them. (refer below screenshot)

Use copy button to copy the formula and paste it into Excel worksheet in D column in row 2. This formula can then be copy-paste to other rows. (refer below screenshot)

Afterwards, insert back the “Name” column by inserting column.

Alternate AI Tools

Alternatively, one might even use below mentioned AI platforms powered by LLM which can generate the formula, as per internal testing

1. ChatGPT (Open AI)
2. Gemini (Google)
3. Claude (Anthropic)

https://www.youtube.com/watch?v=zTmoDW7ws8o&list=PLP0oTm4FOBFKvNBSFYYNPeXpCun70Nj0L&index=120