Excel and ChatGPT
Here ’ s a breakdown of how the formula works :
• The LEN function returns the length of the text string in cell B8 .
• The FIND function searches for the position of the comma in the text string of cell B8 .
• The difference between the length of the text string and the position of the comma gives the number of characters to the right of the comma .
• The RIGHT function extracts that portion of text from cell B8 . So , the formula is essentially taking the text in cell B8 , finding the comma , and returning the text to the right of it . For example , if cell B8 contains “ John Smith , 123 Main Street ”, the formula would return “ 123 Main Street ”.
How cool is that ? It explains the overall goal of the formula , the purpose of each function and concludes with an example . Amazing !
Writing a Formula Let ’ s say you want to write a formula , but aren ’ t sure how . For example , you want to write a formula that adds up a column of values ( column B ), but you only want to include the Debit rows ( as noted in column A ). So , you enter the following prompt : Write an excel formula that adds the values in column b but only when the value in column a is equal to “ Debit ” ChatGPT responds with : The formula to add values in column B based on a condition in column A can be achieved using the SUMIF function . Here ’ s the formula you can use : = SUMIF ( A : A ,” Debit ”, B : B ) Explanation : The SUMIF function takes three arguments :
• The range to check for the condition ( column A in this case )
• The condition to be met (“ Debit ” in this case )
• The range to sum ( column B in this case ) This formula checks column A for the condition “ Debit ” and sums the corresponding values in column B .
Notice that the response not only includes the formula that works with your workbook , it also explains the function arguments and uses your specific data ranges ( not just a generic example ).
Understanding VBA Let ’ s say your organization had an Excel power user write a macro . You have questions about the macro , but the original developer is no longer available . You wonder if ChatGPT can explain the vba code .
So , you copy the code and use the following prompt :
Please explain the following vba code : Sub MyMacro ()
For Each ws In Sheets ws . Name = ws . Range (“ a1 ”). Value
Next ws End Sub
10 CALIFORNIA CPA MAY 2023 www . calcpa . org