Simple Formula Generation Requests

Knowing Excel functions and actually creating formulas are two different things. Combining IF, AND, and OR, or nesting IFERROR with VLOOKUP can get confusing with parentheses and errors pop up frequently.

Now you can just tell AI "please do it like this." It creates even complex formulas effortlessly. It's like having a personal assistant do your Excel work for you.

In this post, we'll learn how to request formulas from AI. From simple calculations to complex conditional statements, we'll explore how to request and receive accurate formulas with practical examples.

1. Basic Formula Request Patterns

The most important thing when requesting formulas is to tell AI precisely "where the data is located." Vaguely asking "calculate this" won't help AI know what to do. But if you specifically say "this in column A, that in column B," it will create the perfect formula right away.

Pattern 1: Specifying Data Location

Let's start with the most basic pattern. Just clearly indicate which columns contain data, and AI will create an accurate formula.

"Column A has product names, column B has unit price, column C has quantity.
Please create a formula in column D to calculate the total amount."

AI Generated Formula:

=B2*C2

Simple, right? A formula that multiplies unit price (column B) × quantity (column C) to get the total amount. It even automatically figured out to start from row 2, creating B2, C2. Put this formula in cell D2 and drag it down to automatically calculate the rest of the rows.

Pattern 2: Including Conditions

"Apply a discount rate in column E, but if column C quantity is 10 or more, apply 10% discount,
otherwise apply 5% discount."

AI Generated Formula:

=D2*(1-IF(C2>=10, 0.1, 0.05))

Pattern 3: Combining Multiple Conditions

"I want to display grades in column F.
- Sales of 1 million won or more: VIP
- 500,000 won or more: Excellent
- Others: Regular"

AI Generated Formula:

=IF(D2>=1000000, "VIP", IF(D2>=500000, "Excellent", "Regular"))

2. Formula Generation by Real-World Case

Case 1: Payroll Statement

Request:

"I'm creating a payroll calculation sheet.
- Column B: Base salary
- Column C: Meal allowance (100,000 won fixed)
- Column D: Transportation (50,000 won fixed)
- Column E: Total payment
- Column F: Income tax (3.3% of total payment)
- Column G: Net payment

Please create formulas for each column."

AI Generated Formulas:

// Column C (Meal allowance)
=100000

// Column D (Transportation)
=50000

// Column E (Total payment)
=B2+C2+D2

// Column F (Income tax)
=E2*0.033

// Column G (Net payment)
=E2-F2

Case 2: Inventory Management

Request:

"I want to create an inventory alert system.
- Column A: Product name
- Column B: Current inventory
- Column C: Safety stock
- Column D: Status (Shortage/Normal/Sufficient)

If inventory is less than safety stock, display 'Shortage',
if it's 2x safety stock or more, display 'Sufficient',
otherwise display 'Normal'."

AI Generated Formula:

=IF(B2<C2, "Shortage", IF(B2>=C2*2, "Sufficient", "Normal"))

Case 3: Attendance Management

Request:

"I want to calculate work hours.
- Column B: Clock-in time (09:00)
- Column C: Clock-out time (18:30)
- Column D: Work hours (in hours)
- Column E: Overtime (hours exceeding 8)
- Column F: Overtime pay (15,000 won per hour)

Please create the formulas."

AI Generated Formulas:

// Column D (Work hours)
=(C2-B2)*24

// Column E (Overtime)
=MAX(0, D2-8)

// Column F (Overtime pay)
=E2*15000

3. Advanced Formula Requests

Auto-Generating VLOOKUP

Request:

"I want to look up employee names from the 'Employee Info' sheet's A:D range
using the employee ID in column A and display in column B.
If not found, display 'Not Registered'."

AI Generated Formula:

=IFERROR(VLOOKUP(A2,EmployeeInfo!$A:$D,2,FALSE),"Not Registered")

INDEX-MATCH Combination

Request:

"Instead of VLOOKUP, I want a more flexible method to
search by product name (column C) in the 'Product DB' sheet
and retrieve the price (column B)."

AI Generated Formula:

=INDEX(ProductDB!$B:$B, MATCH(A2,ProductDB!$C:$C,0))

SUMIFS with Multiple Criteria

Request:

"From sales data, I want to sum only sales where:
- Region is 'Seoul'
- Date is March 2025
- Product is 'Type A'"

AI Generated Formula:

=SUMIFS(Sales!$E:$E,
        Sales!$B:$B, "Seoul",
        Sales!$C:$C, ">=2025-03-01",
        Sales!$C:$C, "<=2025-03-31",
        Sales!$D:$D, "Type A")

4. Text Processing Formulas

Extracting Birth Date from Social Security Number

Request:

"Extract birth date from the social security number in column A (123456-1234567)
in YYYY-MM-DD format."

AI Generated Formula:

=DATE(
  IF(LEFT(A2,2)>25, 1900+LEFT(A2,2), 2000+LEFT(A2,2)),
  MID(A2,3,2),
  MID(A2,5,2)
)

Extracting Domain from Email

Request:

"Extract only the domain after @ from the email address in column B."

AI Generated Formula:

=RIGHT(B2, LEN(B2)-FIND("@",B2))

// Or
=MID(B2, FIND("@",B2)+1, 100)

Converting Phone Number Format

Request:

"Convert the number in column C (01012345678) to 010-1234-5678 format."

AI Generated Formula:

=LEFT(C2,3)&"-"&MID(C2,4,4)&"-"&RIGHT(C2,4)

5. Date Calculation Formulas

Calculating Years of Service

Request:

"Calculate years of service from hire date (column A) to today,
displaying to the first decimal place."

AI Generated Formula:

=ROUND((TODAY()-A2)/365.25, 1)

// Or more precisely
=DATEDIF(A2, TODAY(), "Y")&" years "&DATEDIF(A2, TODAY(), "YM")&" months"

Project Progress Rate

Request:

"Calculate the current progress rate (%) based on
project start date (column B) and end date (column C)."

AI Generated Formula:

=ROUND((TODAY()-B2)/(C2-B2)*100, 0)&"%"

Business Days Calculation

Request:

"Calculate the date 20 business days from today.
(excluding weekends)"

AI Generated Formula:

=WORKDAY(TODAY(), 20)

// To exclude holidays too
=WORKDAY(TODAY(), 20, HolidayRange)

6. Conditional Aggregation Formulas

Multi-Sheet Totals

Request:

"I want to sum the D5 cell values from each sheet
from January to December."

AI Generated Formula:

=SUM('January:December'!D5)

Counting Unique Values

Request:

"I want to count unique values in column A excluding duplicates."

AI Generated Formula:

=SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100))

// Microsoft 365
=COUNTA(UNIQUE(A2:A100))

Conditional Average

Request:

"Calculate the average for students with scores of 60 or above."

AI Generated Formula:

=AVERAGEIF(B2:B50, ">=60")

7. Effective Request Methods

✅ Good Request

"From the employee database:
- Column A: Employee ID
- Column B: Name
- Column C: Department
- Column D: Salary

Please create a formula to count employees with salary of 3 million won or more."

❌ Bad Request

"Calculate something from employee data"

8. Formula Optimization Requests

Request:

"Can you simplify this formula?
=IF(A2>100,IF(A2>200,IF(A2>300,"A","B"),"C"),"D")"

AI Optimized Formula:

=IFS(A2>300, "A", A2>200, "B", A2>100, "C", TRUE, "D")

// Or using SWITCH
=SWITCH(TRUE,
  A2>300, "A",
  A2>200, "B",
  A2>100, "C",
  "D"
)

Conclusion

Tips for AI-powered formula generation:

• ✅ Clearly explain data structure
• ✅ Specifically state desired results
• ✅ List all special conditions
• ✅ Test and validate generated formulas

In the next post, we'll cover analyzing actual sales data and creating summary reports.