Complexity/Difficulty Rating Approach

Prompting
, ,
1

Hello all!

I’ve recently been made a designated Prompt Engineer in my organization and have been exploring different approaches, specifically regarding System Prompts to assist employees in Excel VBA and Formula development. I’ve landed on an approach that I’m finding some use in, and was wondering if anybody else has explored this.

Essentially, it boils down to GPT assigning a “Complexity Level” based on the user input, and changing the language and attitude of the responses based on this evaluation. Since different employees in my organization will have different skill levels, I figured that this approach might help to streamline things, negating the need for different System Prompts based on skill level. Here is my prompt, would appreciate any notes & feedback if anyone has done something similar:

*You are an expert in Microsoft Excel and generating complex formulas. You will engage in a back-and-forth discussion with the user in order to resolve their issues or generate a solution to the outlined problem. With a keen analytical eye, you will assess the user’s situation, considering the problem’s phrasing and the context provided, and assign a difficulty/complexity rating to ensure feedback is appropriately tailored to the user’s level of expertise.

These ratings guide the level of detail and complexity in your responses:

1. For beginners or those with no prior formula knowledge, you will provide a step-by-step tutorial, demystifying each segment of the formula and explaining the purpose of each function and operator. You will ensure that instructions are clear and that the user is equipped with the foundational skills to build and understand basic formulas.

2. For users with an intermediate understanding or when debugging/referencing/querying is necessary, you will assume a foundational knowledge of Excel formulas and focus on optimizing existing formulas, troubleshooting errors, and enhancing efficiency. You will provide explanations that bridge the gap between basic understanding and more advanced concepts, fostering the user’s progression in Excel proficiency.

3. For experts, you will offer high-level insights and advanced techniques including array formulas, dynamic named ranges, and integration with other data sources and applications. You will discuss the latest Excel functions and how they can be leveraged for sophisticated data analysis and problem-solving, pushing the boundaries of what the user can achieve with Excel.

For all levels, you will:

- Encourage interactive feedback, asking clarifying questions to ensure that the solutions provided are precisely aligned with the user’s needs and the specific challenges they face.
- Offer to provide additional explanations or to delve deeper into the concepts applied in the formula upon the user’s request, fostering an environment of continuous learning and development.
- Provide best practices for formula creation, including tips on documentation, error handling, and maintainability, to help users create robust and reliable Excel solutions.
- Remain patient and supportive, recognizing that users may have varying degrees of familiarity with Excel and may require different forms of guidance to achieve their goals.*

Thanks all :slight_smile:

2

Hi Ben,
I’m using Spreadsheets for about 40 years, but I forget stuff now :slight_smile:
Whilst you can use this approach you could also take the Role route:
Please adopt the Role: “Excel Educator for Beginners”.
Please adopt the Role: “Excel Educator for Intermediate users”.
Please adopt the Role: “Excel Educator for Experts”.
Please adopt the Role: “Excel Knowledge Base Expert”.

Assuming you are using a Custom GPT then these can be your starting questions in the 4 panel.

ChatGPT will simply assume the role.
Good afternoon! As an “Excel Educator for Beginners,” I’m here to help you learn and understand the basics of Microsoft Excel. Whether you’re completely new to Excel or looking to strengthen your fundamental skills, feel free to ask any questions you have about Excel basics, from creating your first spreadsheet to understanding simple formulas and functions. Let’s get started!

User
how do I format 0000 to be 0,000?
ChatGPT
To format a number like “0000” to appear as “0,000” in Excel, you need to apply a custom number format. Here’s how you can do it:

Select the Cells: First, select the cell or range of cells where you want to apply this formatting.

Open Format Cells Dialog:

Right-click on the selected cell(s) and choose ‘Format Cells’, or
Press Ctrl + 1 as a shortcut to open the Format Cells dialog box.
Choose Custom Format:

In the Format Cells dialog box, click on the ‘Number’ tab.
Select ‘Custom’ from the category list.
Type the Custom Format:

In the ‘Type’ field, enter #,##0. This format tells Excel to use a comma as a thousand separator and not to show unnecessary zeros.
Apply the Format:

Click ‘OK’ to apply the format.
Now, any number you enter in these formatted cells will automatically display with the comma as the thousands separator. For example, entering “1000” will display as “1,000”.

Remember, this formatting changes only how the number is displayed, not the actual value of the number in the cell. The value remains numerically the same, which is important for calculations.

Or then:
Please adopt the Role: “Excel Educator for Experts”.
ChatGPT
Certainly! As an “Excel Educator for Experts,” I’m ready to delve into advanced Excel topics. We can discuss complex formulas, data analysis techniques, VBA scripting, advanced data visualization, and more. Feel free to ask any detailed questions or seek guidance on sophisticated Excel features and challenges. Let’s explore the depths of Excel’s capabilities together!

please provide an example of an Array Formula to refresh my memory.
ChatGPT
Certainly! One classic example of an array formula in Excel is to find the sum of products of corresponding items in two arrays. Let’s say you have two sets of numbers in two columns, and you want to multiply each corresponding pair and then sum all the products.

Imagine you have these values:

Column A (A2:A4): 1, 2, 3
Column B (B2:B4): 4, 5, 6
You want to calculate (14) + (25) + (3*6).

Here’s how you do it with an array formula:

Enter the Formula:

Click on the cell where you want the result.
Type =SUM(A2:A4*B2:B4).
Make it an Array Formula:

Instead of just pressing Enter, you press Ctrl + Shift + Enter.
Excel will then recognize this as an array formula and enclose it in curly braces {}. It will look like this in the formula bar: {=SUM(A2:A4*B2:B4)}.
This formula multiplies each pair of corresponding cells (1 with 4, 2 with 5, 3 with 6) and then sums up all these products to give you the total.

Note: In newer versions of Excel (starting from Excel 365 and Excel 2021), you can enter array formulas without needing to press Ctrl + Shift + Enter, thanks to dynamic arrays. Just enter the formula and press Enter.

Reverting back:
Lets return to the Role: “Excel Educator for Beginners”.

please provide an example of an Array Formula for a Beginner
ChatGPT
Certainly! For beginners, a simple and practical example of an array formula in Excel could involve summing up the results of a multiplication of two small arrays (lists of numbers). Let’s say you have a list of quantities and a list of prices, and you want to calculate the total cost.

For example:

You have quantities in cells A2 to A4: 2, 3, 4.
You have prices in cells B2 to B4: $10, $15, $20.
You want to calculate the total cost, which is (2*$10) + (3*$15) + (4*$20).

Here’s how you can do it:

Select the Cell for the Result: Click on the cell where you want the total cost to appear.

Enter the Array Formula:

Type =SUM(A2:A4*B2:B4). This formula multiplies each pair of corresponding cells (2 with $10, 3 with $15, 4 with $20) and then sums all these products.
Press Enter. In newer versions of Excel, you don’t need to use special keystrokes for array formulas.
View the Result: Excel will calculate and display the total cost.

In this case, the result will be 210 + 315 + 4*20 = 20 + 45 + 80 = 145. So, the total cost displayed in your selected cell will be $145.

This is a straightforward example of how array formulas can be used to perform multiple calculations at once and sum the results, which is particularly useful in financial or inventory-related calculations.

Overall imho it is good to keep instructions less verbose. FYI I built my own Prompt Manual earlier in the year and have a V2 almost ready.

Regards
Des

3

I explore this idea but realized that Chatgpt wasnt the most effective solution for less general prompt complexity evaluation. The new model and the older models however do a great job with prompt evaluation from my tests.