Catalog

How to Use Wps AIChatgpt to Write Formulas Comprehensive Guide for Beginners

April 16, 2024 1.9K views

In the realm of data, Excel reigns supreme - a double-edged sword of power and perplexity. Yearning for efficiency, we ponder: Can Excel AI unlock its true potential, simplifying formulas and freeing us from data's entangled grasp? The answer whispers resolutely: Embrace Excel AI, witness simplicity and speed entwine, as tables materialize effortlessly, unburdened by complexity or graphs.

How to Use WPS AI to Write Formulas

WPS AI is a ChatGPT-like system that uses artificial intelligence to deliver intelligent assistant services for workplace settings. It is compatible with a wide range of office programs, including document editing, form processing, presentation building, and others. WPS AI offers strong text generating skills that can assist users with a variety of text creation activities like content creation, summary, translation, and more.

Advantages of WPS AI:

  • Time-Saving Wizardry: Speed up document creation and spreadsheet tasks with AI-powered automation.

  • Simplified Workflows: WPS AI's intuitive interface and natural language processing simplify complex tasks.

  • Error-Free Outputs: Enhance accuracy and reliability by leveraging AI's expertise in data handling and analysis.

Steps

Step 1 Open an Excel spreadsheet and select the cell where you want to write the formula.

Step 2 Click the Formulas tab and then click the WPS AI button.

Step 3 In the WPS AI dialog box, type a brief description of the formula that you want to write. For example, you could type "sum of cells A1 and A2".

Step 4 WPS AI will generate a list of possible formulas that match your description.

Step 5 Select the formula that you want to use and click OK.

How to download WPS AI

Step 1 To download WPS AI, you can visit the WPS AI website: https://www.wps.ai/

Step 2 Click on the Free Download button

Click Free download

Click Free download


Step 3 Select the operating system that you are using.

 Choose your option

Choose your option


Step 4 The file will be downloaded automatically

Downloaded file

Downloaded file


Step 5 After approval, customers will get an email from WPS AI with instructions on how to download and install WPS AI on their systems. Once the installation is complete, customers may activate WPS AI on their systems and take use of the numerous functions it provides.

How to Use ChatGPT to Write Formulas

Method 1: Excel Plugin for ChatGPT

Step 1 Install the ChatGPT Excel Plugin from the website.

Step 2 Open Excel and access ChatGPT through the newly added menu or icon.

Authenticate or connect to the ChatGPT service if required.

Step 4 Type your formula-related request, and ChatGPT will respond with suggestions.

Step 5 Insert the desired formula into Excel by clicking on the provided option.

Method 2

Step 1 Enable the Developer tab in Excel options.

Step 2 Access the Visual Basic for Applications (VBA) Editor from the Developer tab.

Step 3 Create a new module in the VBA Editor.

Step 4 Write VBA code to integrate ChatGPT's API or SDK for formula suggestions

(Sub chatGPT()

 

 Dim request As Object

 Dim response As String

 Dim text, API, api_key, DisplayText, error_result As String

 Dim status_code As Long

 Dim cellr As Range

 Dim prompt, rng As Range

 

 'API Info

 API = "https://api.openai.com/v1/chat/completions"

api_key = "sk-xxxxxxxxxxxxxxxxxxxxxxxxxx"


 If api_key = "" Then

       MsgBox "Error: API key is blank!"

       Exit Sub

   End If

   

 Set prompt = Range("B3")

 Set cellr = prompt.Offset(1, 0)

 Set rng = Range(prompt.Offset(1, 0), prompt.Offset(2000, 0))

 rng.Clear

   

 'Input Text

   If prompt.Value = "" Then

       MsgBox "Error: Cell " & prompt.Address(RowAbsolute:=False, ColumnAbsolute:=False) & " is blank!"

       Exit Sub

   End If


 text = Replace(prompt.Value, Chr(34), Chr(39))

 text = Replace(text, vbLf, " ")


 'Create an HTTP request object

 Set request = CreateObject("MSXML2.XMLHTTP")

 With request

    .Open "POST", API, False

    .setRequestHeader "Content-Type", "application/json"

    .setRequestHeader "Authorization", "Bearer " & api_key

    .send "{""model"": ""gpt-3.5-turbo"",  ""messages"": [{""content"":""" & text & """,""role"":""user""}]," _

         & """temperature"": 1, ""top_p"": 0.7, ""max_tokens"": 2048}"

  status_code = .Status

  response = .responseText

 End With


 'Extract content

 If status_code = 200 Then

   DisplayText = ExtractContent(response)

 Else

   DisplayText = ExtractError(response)

 End If

   

 'Put response

 cellr.Value = DisplayText

 

 'Split to multiple rows

 Call SplitTextToMultipleRows(cellr)

 rng.WrapText = True

   

 'Clean up the object

 Set request = Nothing


End Sub


Sub SplitTextToMultipleRows(cell As Range)

   Dim splitArr() As String

   Dim delimiter As String

   delimiter = "\n"

   

   splitArr = Split(cell.Value, delimiter)

   For i = LBound(splitArr) To UBound(splitArr)

       x = splitArr(i)

       If Left(Trim(x), 1) = "=" Then

           x = "'" & x

       End If

       cell.Offset(i, 0).Value = Replace(x, "\""", Chr(34))

   Next i

   

End Sub


Function ExtractContent(jsonString As String) As String

   Dim startPos As Long

   Dim endPos As Long

   Dim Content As String

   

   startPos = InStr(jsonString, """content"": """) + Len("""content"": """)

   endPos = InStr(startPos, jsonString, "},") - 2

   Content = Mid(jsonString, startPos, endPos - startPos)

   Content = Trim(Replace(Content, "\""", Chr(34)))

   

   'Fix for excel forumulas as response

   If Left(Trim(Content), 1) = "=" Then

     Content = "'" & Content

   End If

   

   Content = Replace(Content, vbCrLf, "")

   Content = Replace(Content, vbLf, "")

   Content = Replace(Content, vbCr, "")

   

   If Right(Content, 1) = """" Then

     Content = Left(Content, Len(Content) - 1)

   End If

   

   ExtractContent = Content


End Function


Function ExtractError(jsonString As String) As String

   Dim startPos As Long

   Dim endPos As Long

       startPos = InStr(jsonString, """message"": """) + Len("""message"": """)

       endPos = InStr(startPos, jsonString, """")

       If startPos > Len("""message"": """) And endPos > startPos Then

           ExtractError = Mid(jsonString, startPos, endPos - startPos)

       Else

           startPos = InStr(jsonString, """code"": """) + Len("""code"": """)

           endPos = InStr(startPos, jsonString, """")

           If startPos > Len("""code"": """) And endPos > startPos Then

             ExtractError = Mid(jsonString, startPos, endPos - startPos)

           Else

             ExtractError = "Unknown error"

           End If

       End If

End Function

Step 5 Assign the macro to a button or shortcut for easy access.

Step 6 Use the macro to get formula suggestions in Excel.

Method 3

You can also run ChatGPT as an excel function. The benefit of running it as function is that you can paste it down on multiple cells. See the animated image below how to use the function.

Step 1 Access ChatGPT: You can use ChatGPT as an Excel function to generate responses based on your input.

Function Syntax: The function syntax is: AIAssistant(text, [word_count]).

  • text: Input your prompt or question here.

  • word_count (optional): Specify the maximum number of words in the response.

Step 2 Set up the Function: a. Enter your prompt/question in one cell. b. In another cell, use the formula =AIAssistant(A1, [word_count]), where A1 is the cell with your prompt, and [word_count] is the optional word limit.

Step 3 Apply to Multiple Cells: Copy the formula with the AIAssistant function to other cells to get responses for multiple prompts.

Example - Extracting Capital Cities: a. List countries in Column A. b. In Column B, use the formula =AIAssistant(A1, [word_count]) to get the capital cities for each country. c. Copy the formula to other cells in Column B for other countries.

Limit Response Length: Control response length by adjusting the [word_count] to get more concise answers.

Trustpilot

stars

WPS Office- Free All-in-One Office Suite
  • Use Word, Excel, and PPT for FREE, No Ads.

  • Edit PDF files with the powerful PDF toolkit.

  • Microsoft-like interface. Easy to learn. 100% Compatibility.

  • Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

5,820,008 User

avator

Algirdas Jasaitis
logo

ChatGpt Prompts

ChatGPT can be a valuable tool for writing formulas in Excel, simplifying the process and enhancing your productivity. Explore these prompts to leverage ChatGPT's capabilities and effortlessly generate formulas for various data analysis tasks.

  • "Sum Formula": Generate a formula to calculate the sum of a range of cells, streamlining total calculations.

  • "Average Formula": Get a formula for finding the average of a set of values, aiding in data analysis.

  • "Percentage Formula": Create a formula to calculate percentages, useful for various business scenarios.

  • "IF Function": Generate an IF function to perform conditional calculations based on specific criteria.

  • "VLOOKUP Formula": Get a formula to perform a vertical lookup, extracting data from another table.

FAQs

1. Does Excel have Artificial Intelligence?

Excel doesn't have true AI, but it uses machine learning in features like Flash Fill, Smart Lookup, and dynamic arrays. Third-party add-ins also enhance Excel with AI capabilities for data analysis and productivity. As technology evolves, more AI-powered features are likely to be introduced.

2. What Ethical Considerations Should be Taken into Account When Using AI for Formula Writing?

Ethical considerations for AI in formula writing encompass preventing discrimination and ensuring equitable treatment, transparency in AI workings and user data protection, safeguarding privacy, and accounting for safety and environmental impacts.

Providing understandable AI decisions and involving human oversight aligns AI with human values and regulations, building trust through transparency and accountability. Prioritizing user needs in AI system design, taking responsibility for AI actions and impacts, and considering long-term societal and environmental effects ensure responsible and ethical AI usage.

3. What are the Different Types of AI?

AI can be categorized into different types based on their capabilities and functionalities. The main types of AI are as follows:

  • Narrow AI (Weak AI): Performs specific tasks better than humans within a limited domain (e.g., virtual assistants, image recognition).

  • General AI (Strong AI): Outperforms humans in any intellectual task, possesses human-like intelligence, and can apply judgment in various situations.

  • Artificial Superintelligence (ASI): Surpasses human capabilities in almost every field, including creativity, wisdom, and social skills.

4. What is Excel AI?

Excel AI is the integration of artificial intelligence (AI) technology into Microsoft Excel, one of the most widely used spreadsheet applications. By leveraging AI capabilities, Excel becomes more powerful, efficient, and user-friendly, enhancing data processing, analysis, and automation.

Benefit of AI Tool for Excel

  • Formula Writing: Intelligently suggests formulas based on natural language, saving time and reducing errors.

  • Data Analysis: Uncovers hidden patterns, trends, and insights for more informed decision-making.

  • Automation: Automates repetitive tasks, boosting productivity and efficiency.

  • Data Visualization: Creates interactive and visually appealing charts and graphs effortlessly.

  • Decision-Making: Presents meaningful insights from large datasets, aiding smarter decisions.

Some AI Tools for Excel

  • WPS AI: An AI-powered assistant that can help users write formulas and analyze data in Excel.

  • Microsoft Power Query: A data transformation tool that can help users clean and transform data in Excel.

  • Formula Bot: A web app that uses AI to help users write formulas in Excel.

Summary

In this comprehensive guide, we embarked on a journey to master the art of formula writing with the aid of WPS AI/ChatGPT. Excel, a powerful yet intricate tool, has been made more accessible and efficient through AI integration, empowering beginners to navigate its complexities effortlessly.

WPS AI proved to be a game-changer, simplifying formula writing and empowering us in Excel's realm. Its intuitive interface, time-saving automation, and AI-powered insights streamline data analysis. Embrace WPS AI's power and unlock Excel's true potential, simplifying complex calculations and revolutionizing productivity in your data-driven endeavors.

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.