Article Structure :

• Introduction: Can AI help  build  ‘real life’ business Spreadsheets  and boost productivity.
• List of the practical  spreadsheets solutions we will build .
•  Master ChatGPT prompting, especially for Excel users.
•  ChatGPT Prompt Template for building Excel spreadsheets with AI.
• Checklist for using AI with Spreadsheet.
• Building the Spreadsheets using ChatGPT prompts.
• Videos for each of the Spreadsheet Solution.
• Quick 5 minute Test to see if it’s safe for you or your team members to use Excel with AI.
• Conclusion & Summary- Benefits  & Its limitations in detail.

Please note we will be using ChatGPT version GPT-4 free version   (late 2024 )to test and build these spreadsheet models.

In this article, we explore how AI, especially ChatGPT, is transforming the way we use Microsoft Excel . In particular, its ability  to create practical, ‘real-world’ solutions for everyday workflows .

We have felt the need to update our Online Excel Courses.

In response to the evolving landscape of artificial intelligence, we have updated both our Online Excel Courses  and instructor-led Excel courses. The new curriculum includes a new level on ‘Using Excel with AI” but the existing courses have changed to emphasizes a deeper comprehension of Excel’s fundamental concepts, logic, syntax and mechanics  especially in relation to :

• Relative/absolute references.
• Table structures.
• Linking sheets and workbooks.
• Formatting : Percentages, Dates, Times
• Building formulas with math, logical operators , order of precedence
• The Big Workhorse functions
• Pivot tables structures.

By mastering these core mechanics  and syntax and integrating ChatGPT prompting with Excel in mind, Excel users can significantly boost productivity and efficiency while remaining equipped to identify errors and understand the limitations of AI-driven assistance.

If you have ever completed an Excel course, you will probably know this feeling ?

If you’ve completed an Excel course, you will likely understand the difference between knowing  functions/ formulas and between trying to combine  the  functions, concepts and logics together to create complete working spreadsheet solutions.
This is where things get tricky, and it’s often where users struggle the most.

Excel’s true power lies in integrating functions, techniques, to create real life useful spreadsheets . ChatGPT, within its limits, can step in here, offering significant support in  building these solutions. It can also review existing formulas, suggest more efficient workflows, and flag potential errors.

We will try to build  ‘ real-world’  everyday Business  Spreadsheets  using AI prompts.

In everyday office scenarios, spreadsheets are not just used for basic calculations; they’re used to automate workflows, track performance, and make informed decisions. Here are a few examples of common, ‘real life’  business spreadsheets challenges:

1. Tracking Partial Payments with Conditions and Highlighting Completed Records.

   You’re managing customer payments. You need to track partial payments and the payment needs to be marked as “Completed ” in another column. Your solution needs to combine  SUMIFS ,conditional logic  and  formatting formulas, data validation and understanding relative and absolute references.
   –

2. Search & Compare  Different  lists , Process them and Highlight Certain Records .

   Building this practical spreadsheet  could involve lookups , linking between worksheets, maybe  INDEX & MATCH Functions ,logic operators, references and  conditional formatting.
   –

3. Marking Task Completed  Based on Multiple Criteria before an action(s) happen.

   You need to build a data tracking spreadsheet . A task should only be marked as “Completed” if certain conditions are met: full payments made, the deadline has passed, all subtasks are finished. This spreadsheet  involves using IF statements, XLOOKUPS,   logical operators like AND, relative and absolute references,  checking multiple columns and  pulling data from other sheets, even other workbooks.
   –

4. Cross Footing to Check for Calculation Errors and Give Warning. 

   We have contractors working in two offices- office A and office B and  on different projects.  I want to total up the number of hours worked by each office ,  each contractor, each project and each location . But I also need checks in place to warn of possible errors.

Can ChatGPT Can Help ?

This is where ChatGPT can step in. Instead of explaining individual formulas, ChatGPT can help you combine functions and techniques to build complete solutions. We will use  the examples above and see if  AI might guide you in solving them quickly and effectively.

But first you need to learn how to write effective ChatGPT prompts and specifically how to  use and adapt them when using Microsoft Excel.

Prompt  Template  For Spreadsheets.

Below is the  outline of  our Prompt Template For Excel . You can download the full version at  https://onlineexceltraining.com/prompt_template/    Feel free to pass it on to your  team members, and friends. Also to learn more about the Prompt Template for Spreadsheets, watch the video here.

General Problem Overview

1.Task Description:

Clearly describe the overall task or problem you’re encountering in Excel. Be specific about the action or result you’re trying to achieve.

2.Dataset Context and Structure

3.Dataset Description:
Offer a detailed description of your dataset, including column headers, number of rows, data types, and any relevant cell ranges.

4.Conditions and Constraints:
Mention any specific rules or conditions that apply to your data, such as specific

5.[ Advanced Prompting Techniques: Few-Shot Examples for Guidance]

                     [Chain-of-Thought (CoT) Reasoning]

                      [Tree-of-Thought (ToT) Exploration]

                      Specific Excel Tools, Functions, or Features

6.Expected Outcome (Precision and Directional Prompting)

7.Sample Data and Error Details

8. Version and Platform Information

CheckList for Writing Spreadsheet  Prompts.

Now from our experiments creating prompts to build  Microsoft Excel spreadsheets  , here is a brief  checklist for you. The full checklist is available at https://onlineexceltraining.com/checklistprompts/

1. 1. Supply very detailed context of both the data and the problem. Present information clearly , logically in a step by step manner. In particular, supply a  clear description of any datasets: Also specifying the structure of the data (e.g., number formats, types of data like text, numbers, dates)

   2. Breakdown of Requirements: Instead of a block of text, list the requirements step by step for better clarity. Prioritize the order of operations if the task has multiple steps.

   3. Use directional nudging. ChatGPT often needs to be nudged in the correct direction, often many times.

   4. Paste in a copy of your headers and at least 3 rows of your data at end of prompt with delimiters like “”” data “”

   5. We have found that ChatGPT often ignores relative and absolute referencing and does not always recognize Excel tables .So we always include, when it required in the solution , the text: ‘ Please allow for proper application of relative and absolute references when copying formulas’.    And if using tables ,  ‘I want a solution that leverages Excel structured references since these are Excel Tables’

   6. If you know which functions should to use , let the prompt know. The more directions , it is given , the better. This helps it overcome one of its biggest weaknesses, not supplying  the most efficient optimal solution.

   7. If possible ,give the prompt an example or examples to follow  as in few shot prompting , which is very effective for excel type prompts.

   8. Emphasize and repeat the verbal action you want and we have found it best to start the sentence with the verb. Compare the lists, Count the items, Extract, Calculate etc. They  not only sets direction but avoids ambiguity.

   9. Don’t be surprised that you get slightly different answers from using the same prompt. Remember ChatGPT is large language model. This means, basically , it is just matching patterns. It does not understand the meaning of what you are doing.

   10. Also remember AI models like ChatGPT are not search engines – they’re designed for dialogue. If an initial prompt doesn’t yield the expected result, refine the prompt. Evaluate the clarity of your instructions. Check it against our template and this checklist. Has the model had enough time to “reason ”. Try to  identify any potentially misleading elements in the prompt. The key to success is to iteratively refine your prompt, improving its effectiveness with each iteration to best suit your task.Ask for Testing Instructions

   11. Once a solution is provided, ask for instructions on how to test or verify its effectiveness. For example: “How can I test if this SUMIFS formula works correctly with different inputs?”

   12. If the task involves ambiguous data (e.g., inconsistent text, missing values), ask ChatGPT to account for this. Example: “Please account for inconsistent text in the data before applying formulas.”

   13. It’s important to prompt ChatGPT to consider edge cases like empty cells, zero values, or invalid data entries. For instance: “Include a solution that handles empty cells or invalid inputs gracefully without causing errors in the spreadsheet.

Then Check for these Common Pitfalls:

• Vague Problem Descriptions: Be specific about what you’re trying to achieve.
• Lack of Data Context: Without understanding the structure of your data, solutions may not fit.
• Assuming a Specific Solution: Don’t assume a specific formula or method will solve your problem (e.g., insisting on using VLOOKUP when XLOOKUP or another method might be better).
• Leaving out Key Details: Include error messages, platform versions, and expected results.
• Oversimplification or Overcomplication: Avoid leaving out key data structure details, but also don’t overwhelm with irrelevant details.

 Building Practical Spreadsheets using  our  ChatGPT Prompt Template.

Watch the Spreadsheet and Prompt been built or read the text version below.

First  Spreadsheet Problem:

Lets start off  with a more  straightforward example . We have a list of artists played on a radio station. Each artist is entitled to a royalty of €2 per minute each time their song is played. Our task is to calculate how much each artist is owed.

The main challenges we face are:

1. Extracting a unique list of artists from this large dataset, which contains many duplicate entries.

2. Summing the total minutes each artist was played, then calculating the royalties by multiplying the time by the €2 rate.

Can ChatGPT help me create a solution to calculate the total royalties for each artist?

The Process.

First, let’s look at some possible solutions. Then we will write our prompt and compare ChatGPT solutions to ours.

In relation to getting a unique list of artists there are a number of ways to do this in excel. We could use the ‘remove duplicates tool in the data ribbon .We could use a pivot table or as we are using excel 365 we can use the UNIQUE function.

5To add up the total  minutes played for each artist , the SUMIF OR SUMIFS Function can be used for this.

What is key in this solution is getting the relative and absolute references right. We will  be looking very carefully at ChatGPT solution  especially in relation to the relative and absolute references.

So let’s build our prompt.  It’s based, of course on our Prompts Template  for Excel Solutions.

Prompt:

“You are an Excel expert. I have a dataset in the range B3:E954

, which includes Song Number, Singer, Date, and Minutes played. In cell H1, the royalty fee per minute is set at €2.

Each artist is paid €2 for every minute their songs are played. The ‘Singer’ column which is column C  contains duplicate entries as some artists have multiple songs. I need you to create a formula that:

1. Extracts a unique list of artists (Singers) starting in cell G3.

2. Calculates the total royalty due to each artist, based on the sum of minutes played for all their songs, and displays the corresponding total royalty in cell H3 and be copied down.

Please provide the formula(s) necessary for this. No need for explanations. The full list range is C3:C954. Also Please allow for proper application of relative and absolute references when copying formulas down. Note: I am using Excel 365.”

“””

 “””

Let’s have a look at this prompt.

Firstly, we are giving the prompt its role as an Excel Expert. We are then describing our dataset in detail,  both its location and also the headings. We have also attached a sample at the end of the prompt. We also describe  exactly where the royalty fee is located.

Next , we are describing in detail the task we want the prompt to do . Its written in a logical , clear , step by step approach . Again, we are reminding it of the column names and where they are located column wise.

Finally at the end , we are  summarizing what we want it to do. Also important to note, we’re reminding and emphasizing to the prompt  to allow for full relative and absolute referencing. This is very important . Remember ChatGPT does not understand the meaning of what we’re asking it. It is simply matching patterns.

Also, I only want the formulas, hence I have written ‘No need for explanations’. Leave this out , if you want full explanations for each formula. Finally we note the  version of excel.

ChatGPT Response

It  does very well even though, it is a quite straightforward problem.

=UNIQUE(C4:C954)

=SUMIF($C$4:$C$954,G3,$E$4:$E$954)*H$1

It’s  giving the best solution for the version we told it we are using , namely Excel  365. It uses the UNIQUE   and  the SUMIF functionS and most importantly it gets the absolute and relative references totally correct ,which is the key to this particular problem.

So as off late 20204,  for accurate responses from ChatGPT , it’s important to specify  the context in detail and  clearly , to use directional nudging and remind it to apply relative and absolute referencing/ table structure.

Always, always  double check the results of the suggested formulas. To do this, you can incorporate it into your spreadsheet and make sure the results are what you are looking for. If they aren’t, feeding Chat GPT the expected result or even your workings, might be all it needs to fix the formula. Remember it’s built for dialogue.

Second Spreadsheet  Problem & Solution:
Full Data Tracking System for Partial Payments.

Watch the Spreadsheet and Prompt been built.

General:
As  per image , I need to calculate the total payments made by customers in various amounts, and I want Excel to highlight records  when  certain conditions are met.

Specific:
Clients are paying for their holiday trip in instalments, and we track each payment in a table called payments. In column E, I need to display the total payment for each client. Also In the “Complete” column (H), we need the spreadsheet to display a “C” if the customer has paid in total, signed their  itinerary form  and paid their  insurance fee.
Next, we  want to highlight  every  record  when those same conditions have been meet. Can ChatGPT provide a complete solution for this?

The Process:

Let’s look at some possible solutions then we will write our prompt and compare ChatGPT solutions to ours.
So to calculate  ‘Total Fees, SUMIF  will do.

=SUMIF(Payments[Attending],[@Accepted],Payments[Payment])

It will use the Attending column in the payments table , the criteria will be the name in B4, Alo and it will add up all payments from Alo in the payments table.  ChatGPT might also use the SUMIFS Function.

In the “Complete” column (H), a formula like this could be used

=IF(AND(COUNTA(holidays[@[Travel Itinerary]:[Insurance ]])=2,[@[Total Fees Paid]]=[@FEE]),”C”,””)

The COUNTA function will count the entries in cells F9 and G9 to see if both contain an “x” (indicating completion), and then verify that the total fee matches the required amount. If all conditions are satisfied, the formula will place a “C” in the cell.

For the conditional formatting formula:

=AND($D4=$E4, $F4=”x”, $G4=”x”, $H4=”C”)

will work. ChatGPT hopefully, if we write our prompt correctly, will use  standard direct references and also get the relative and absolute referencing  totally right.

So let’s build our prompt . Its based , of course,  on our ChatGPT Prompts Template  for building spreadsheet . So from using this template, here is our prompt.

The Prompt:

“I have two Excel tables:

1. holidays table

   • Contains seven columns. The fourth column (E3) is labelled ‘Total Fees Paid’. Client names are in the first column (B3) and is labelled “Accepted”.

2. Payments table

   • Contains two columns: ‘Attending’ (K5) and ‘Payment’ (L5). The ‘Attending’ column has client names, and ‘Payment’ records the payment amounts.

Task 1:
I need a formula for the ‘Total Fees Paid’ column (in the holidays table) that sums the payments from the payments table for each client (matching by client name). Use Excel structured references since these are Excel tables.

Task 2:
In the “Complete” column (H), we need the spreadsheet to display a “C” if the customer has met all the following conditions:

1. The total fee has been paid.
2. The travel itinerary has been signed. (there is an ‘x’ in the cell)
3. The insurance payment is complete. (there is an ‘x’ in the cell)

Can you create a formula to do this.

Task 3:
For each record in the holidays table (starting from row 4):

• Check if the values in the ‘Total Fees Paid’ column (E) and the ‘Fee’ column (D) are equal.
• Check if there is an “x” in the ‘Travel Itinerary’ column (F) and the ‘Insurance’ column (G).
• Check if there is a “C” in the ‘COMPLETE’ column (H).

If all conditions are met, apply conditional formatting to the row. Please provide a formula using standard cell references (not table references) and ensure proper use of relative and absolute references. No need for explanations. The headers are in row 3, and the first record starts in row 4.” I am using Excel 365.

“””

“””

How the Prompt was built?

Let’s have a look at this prompt and see how it is constructed. We describe the dataset in detail, both its location and its headings. We also attached a sample at the end of the prompt.

Next, we  describe in detail the tasks we want the prompt to do .The three tasks are  written in a logical , clear , step by step manner . Again, we are reminding the prompt  of the  column  names and where they are located column wise .We let it know we are dealing with tables and use the term ‘Use Excel structured references since these are Excel tables”.

The conditions in Task 2  and  3 are laid out clearly.  Again, note the important nudging techniques term ‘Please provide a formula using standard cell references (not table references’. We don’t want  ChatGPT to use tables structure in the conditional format formula .Excel’s conditional formatting doesn’t handle structured references  the same way as normal formulas. And to make ensure correct references are applied , we added “ensure proper use of relative and absolute references.”

Remember ChatGPT does not understand the meaning of what we’re asking it to do . It is simply matching patterns.
Now  I only want the prompt to return the  formulas, hence I have written ‘No need for explanations’.  Leave this out , if you want full explanations for each formula. Its ends up with the Excel Version we are using. Again, very important when building spreadsheet prompts. A sample of the data is then added to the end of the prompt with delimiters.

ChatGPT Response:

=SUMIFS(payments[Payment], payments[Attending], [@Accepted])

While we used the SUMIF function, it used the SUMIFS   function , which  is fine and also correct. It uses the correct excel table syntax between both the holiday and payments  tables.

=IF(AND([@Total Fees Paid]=[@FEE], [@Travel Itinerary]=”x”, [@Insurance]=”x”), “C”, “”)

Now for the H column, the “complete” column, it  uses a slightly different formula than us. We used the COUNTA function  , but it has used  a more direct approach and probably a better approach and it work fine.

=AND($E4=$D4, $F4=”x”, $G4=”x”, $H4=”C”)

For the conditional format formula, again, it is  correct . It is using standard cell referencing, the correct cells references and most importantly, the relative and absolute referencing are applied properly.

It should be noted that when we left out  ‘and I want a solution that leverages Excel structured references since these are Excel Tables“, the majority  of the responses from ChatGPT did not recognize that we were using Excel Tables.

Third Spreadsheet  Problem & Solution:
Comparing Two Lists & Processing the Data.

Video of this Spreadsheet and Prompt been built.

The Problem:
We run a club and we have 2  lists  called  paid members  and  one called overdue members.

The paid members  list is the latest updated  list showing us all the members that   have paid their current membership fees. And list 2, Overdue Members , are the members who have overdue payments.

We want to: Compare both lists to identify:

• Which overdue members have now paid their fees  i.e., members who appear in both lists .
• Also I want to identify which members are still overdue , appear in the overdue list but not in the paid list.
• Finally I want to identify members who paid their fees on time , that is  they appear  only in the paid list, not in the overdue list.

I also now want , after getting this updated payments list , to calculate the total fees paid by members who were overdue but have now paid. I will put this formula in this cell here.

And finally, as you can see , I want to  apply  conditional formatting to visually highlight the various payment positions:

• We will color Green members who have paid their fees and were overdue that is they appear in both lists.
• We will color Red Members who are still overdue that is they are only in the overdue list.
• And finally we will color in Blue  Members who paid on time  that is they appear only in the paid list.

Possible Solutions:

Now, we will look at possible solutions , which we will then  compare with the solutions offered by ChatGPT. The main  task could be completed by using  a VLOOKUP Functions like this one here. But a  MATCH function could also be used.

=IF(NOT(ISERROR(VLOOKUP(A4, $E$4:$E$8, 1, FALSE))), “Paid Overdue”, “Paid On Time”)

Now to Calculate the Total Fees Paid by Overdue Members , we could  use a  SUMIFS or a SUMIF   function like this one here.

=SUMIFS($B$4:$B$13, $C$4:$C$13,”Paid Overdue”)

Finally, let us  look at possible formulas we can use to apply the conditional formats .

          ‘=$C4=”Paid Overdue”

          =$C4=”Paid On Time”

             =ISNA(VLOOKUP(E4, $A$4:$A$13, 1, FALSE))

CheckList for Excel Prompts.

Now ,before we build the prompt for this example , certain prompting techniques   stand out , especially  when building prompts for Excel , as of late 2024.

• Supply very detailed context in terms of both the data and the problem. Present information clearly and logically. In particular, supply a Clear Description of any datasets, both column headings and range descriptions. Paste samples at end with delimiters.

• Breakdown of Requirements: Instead of a block of text, list the requirements step by step for better clarity.
• Use directional nudging.  ChatGPT often needs to be nudged in the correct direction . For example, copy the formula down the range A2:A10.

• We have found that ChatGPT often ignores relative and absolute referencing and does not always recognize Excel tables . So we always include, when it is required in the solution , the text:
  ‘ Please allow for proper application of relative and absolute references when copying formulas’.
  And if using tables ,  ‘I want a solution that leverages Excel structured references since these are Excel Tables’

• If you want the prompt to use certain  functions , let the prompt know. The more directions it gets, the better. This helps it overcome one of its biggest weaknesses, not supplying  he most efficient optimal solution.

• Emphasize and repeat the verbal action you want and we have found it best to start the sentence with the verb . “Compare the lists”, “Count the items” etc

• Don’t be surprised that you get slightly different answers from using the same prompt. Remember ChatGPT is large language model, it is just matching patterns, it does not understand the meaning of what you are doing.

• Remember AI models  like ChatGPT are not  search engines – they’re designed for dialogue. If an initial prompt doesn’t yield the expected result, refine the prompt. Evaluate the clarity of your instructions. Check it against our template and checklist.
  Has the model had enough time to “reason ” and identify any potentially misleading elements in the prompt. The key to success is to iteratively refine your prompt, improving its effectiveness with each iteration to best suit your task

Our Prompt

“I have two lists of club members in Excel:

• A current list of paid members in the range A4 to B8
• A list of overdue members in the range E3 to E8

I need to generate formulas that:

•  Directly compare both lists (using VLOOKUP, MATCH or similar) to identify which overdue members have now paid their fees. If a member is found on both lists (i.e., they were previously overdue but have now paid), label them ‘Paid Overdue’. If a member is only found in the paid list (i.e., they paid on time and were never overdue), label them ‘Paid On Time’. The comparison result should go into cell C4 and be copied down.

• Calculate the total subscription fees paid by members who were previously overdue but have now paid.

• Apply conditional formatting with the following rules:

                Green: members who were overdue but have now paid (appear in both lists).
                Blue: members who paid on time (only appear in the paid list)
                Red: members who are still overdue (only appear in the overdue list, but not the paid list).

Please provide the exact formulas. Use the comparison result in column C directly for conditional formatting . Use Direct cell references as much as possible.  Ensue proper use of relative and absolute references when copying.”. Please supply the required formulas, including the formula to  COMPARE the two lists , in tabular form. There is no need for explanations.  I am using Excel 2019.

ChatGPT Response.

Let’s input the prompt into ChatGPT and let’s see what it comes up with.

And here are  its solutions.

=IF(ISNUMBER(MATCH(A4,$E$3:$E$13,0)),”Paid Overdue”,”Paid On Time”)

Now  it is using a Match function .We used  the VLOOKUP function but both are suitable for this task  and work correctly.

=SUMIFS($B$4:$B$13, $C$4:$C$13,”Paid Overdue”)

We can see that it used the SUMIFS Function which of course  is fine. And the  formulas for conditional formatting are exactly the same as the ones that we had created  ourselves , except , again, is used the MATCH  function , which is fine.
So the response from ChatGPT  has been very  good  but the  importance of accurate prompting  should  be noted in getting it  to work accurately. But even if it does not work properly the first time, remember prompt Writing is  an Iterative Process.
AI models  like ChatGPT are not  search engines – they’re designed for dialogue. If an initial prompt doesn’t yield the expected result, refine the prompt.
Evaluate the clarity of your instructions. Check it against our template and checklist. Has the model had enough time to “reason ” and identify any potentially misleading elements in the prompt. The key to success is to iteratively refine your prompt, improving its effectiveness with each iteration to best suit your task.

Fourth Spreadsheet Problem & Solution.
Reconciling & Highlighting Payments System.

The Problem.
General:

Summing Values Based on Conditions and Highlighting them. This type of conditional summing is crucial in tasks like reconciling payments.

Specific:

We need to sum payments over $500, but only if the payment status in another column is marked as ‘Approved’ and the date in another column is greater than 31/12/2023.  Also I want  all records that match these three criteria highlighted.

The Prompt:

“You are an Excel expert. I have a data range from A1:D18 , with the headers: ‘Payment Id’, ‘Payment’, ‘Status’, and ‘Payment Date’. I need a formula in Excel 365 that sums the values in the ‘Payment’ column (B) only when:

• The ‘Payment’ is greater than $500,
• The ‘Status’ is marked as ‘Approved’ in column C, and
• The ‘Payment Date’ in column D is after December 31, 2023.

Additionally, I want to apply conditional formatting to highlight records that meet all these criteria. Can you write the formula I need for this conditional formatting. What potential issues could prevent this formula from working correctly, and how can I address them?

ChatGPT Response:

=SUMIFS(B2:B18, B2:B18, “>500”, C2:C18, “Approved”, D2:D18, “>31/12/2023”)

=AND($B2>500, $C2=”Approved”, $D2>DATE(2023,12,31)) 

Both formula when pasted onto our spreadsheet  , work effectively. A very good response from AI.
In terms of potential Issues and solutions, ChatGPT  lists – Data type mismatches, date formatting, case sensitivity in Status . See full response at  https://chatgpt.com/share/66fdb955-ecdc-800f-94b7-8bf5a5560622

Fifth  Spreadsheet Problem & Solution.
Searching Datasets from Right to Left. 

The Problem:

I need to identify the largest invoice issued today, along with the name of the customer.
To do this, I must find the value of the largest invoice and then search my list of invoices from right to left. So  I cannot use VLOOKUP  and  as I’m using Excel 2016 I don’t have access to XLOOKUP. Can ChatGPT help me with this?

Prompt:

You are an expert Excel coder. I have a list of data in cells B5:D406 , with the headers: “ID”, “CusName”, and “InvoiceAmt.” I need a formula to:

1. Identify the largest invoice value from the “InvoiceAmt” column (column D).
2. Find this value in column D and return the corresponding customer name from column C.

The formula must be robust enough to continue working if rows or columns are added in the future. Please ensure it covers all possible scenarios, and explain your approach. Note: I am using Excel 2016.

“””

“”” 

ChatGPT   Response:

It returns this solution:

=INDEX(C5:C406, MATCH(MAX(D5:D406), D5:D406, 0))

which is good solution and precise usage of the INDEX and MATCH Functions.

In relation to inserting rows, it rightly suggests using structured table ,  or =INDEX(C:C, MATCH(MAX(D:D), D:D, 0)).  In relation to inserting columns, it suggests using relative column references (e.g., C:C, D:D) to ensure the formula still works if columns are inserted or shifted. But it does  not mention what would happen if we had duplicate invoice amounts. Also note , this detailed list of weakness and solutions only happened after using the nudging technique text –“ Please ensure it covers all possible scenarios, and explain your approach.”

Sixth  Spreadsheet Problem & Solution.
Cross Footing my Spreadsheets.

Problem.

I want to  check that our calculations are correct. Can ChatGPT create formulas to total up hours worked in the various tables and then create a cross footing formula to warm of errors.

Specifically:

 We have contractors working in two offices- office A and office B and  on different projects.  I want to total up the number of hours worked by each office , by  each contractor, on each project and in each location . Finally check  they all equal each other.

Prompt:

“

You are an Excel Expert. We have 2 tables , both have the same column headings:  “Contractor”  , “Tasks”, “Time(Hours).
The first table called officeA  starts in cell B3 and the second table called office starts in G3.
They are keeping  tract of the hours worked on various projects by each contractor in 2 different offices, office a and office b.

Now I have 3 others tables :

“Tasks”  table which has 2 headings and starts in cell L3 .   Tasks , Total Hours.  It is summing the hours spent on each task.
contractors table which has 2 headings and starts in cell L10:  Contractors  , Total Hours. It is summing the hours worked by each contractor.

“Locations” table which has 2 headings and starts in cell O3:  location   , Total Hours.  It is summing the hours worked at each office location .

Can you create formulas which will sum up the total hours in these  tables for each item :  tasks, contractors and locations. ‘I want a solution that leverages Excel structured references since these are Excel Tables’
Finally , can you create a  cross footing   formula  to make sure the total Hours  of these 3 tables : tasks, contractors and locations are equal to each other . If not, I want a message saying “ warning, possible error in calculations “.  I am using Excel 365″

“””

officeA table sample

Office B Sample

tasks sampe

contractors sample

locations table

ChatGPT  Response :

See full response here https://chatgpt.com/share/6701ba85-015c-800f-9ad8-4bd735a0d520

For tasks, after plugging  in the formula

=SUMIF(officeA[Tasks], L4, officeA[Time (Hours)]) + SUMIF(officeB[Tasks], L4, officeB[Time (Hours)])

it works perfectly.

For contractors ,  it gives

 =SUMIF(officeA[Contractor], L11, officeA[Time (Hours)]) + SUMIF(officeB[Contractor], L11, officeB[Time (Hours)])
 which again is correct.

For locations,
=SUM(officeA[Time (Hours)])  and =SUM(officeB[Time (Hours)])   which are also correct.

And the cross foot formula , it suggestion is

=IF(SUM(tasks[Total Hours]) = SUM(contractors[Total Hours]) = SUM(locations[Total Hours]), “Totals Match”, “Warning, possible error in calculations”)

This , unfortunately, its not a usable formula .The problem is you can’t use = to compare multiple values in a single operation. Instead, you need to break it down into two comparisons and use the logical AND function to combine them.
Lets go back and see if ChatGPT realise its mistake and can it  fix it .

It comes back with
  =IF(AND(SUM(tasks[Total Hours]) = SUM(contractors[Total Hours]), SUM(contractors[Total Hours]) = SUM(locations[Total Hours])), “Totals match”, “Warning, possible error in calculations”)

This is  correct and ChatGPT explains its reasoning and the error it made.
Again, it shows the importance of checking these suggested  formulas in your workbook  and the need to have a strong knowledge of Excel to notice and understand these errors.

Here’s a prompt you can use to ask ChatGPT to check its work on an Excel formula:

“Could you please verify the Excel formula you previously provided? The formula is [insert formula]. I’d like you to check if it will achieve [describe your intended result]. If necessary, please make any corrections or suggest improvements to ensure it works as expected.”

[Improvement with Nudging ]

Finally can you see any weaknesses in the formula . If so, can you create a more effective formula which would allow for  eventualities  and explain your reasoning .

Version:  [ I am using Excel ]