IMAGINARY function

IMAGINARY extracts the imaginary coefficient from a complex number, returning its numeric value for use in mathematical calculations and analysis.

=IMAGINARY(inumber)

Generate a IMAGINARY formula

Describe what you need. The generator will reach for IMAGINARY where IMAGINARY is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How IMAGINARY reads its arguments
inumberrequiredIMAGINARY →
ArgumentRequiredDescription
inumberRequiredA complex number in text format ("a+bi", e.g., "92+100i") or created by COMPLEX(real, imaginary). If not in valid complex format, returns #VALUE! error.

Returns

A number representing the imaginary coefficient of the supplied complex number.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Extract maximum score encoded as imaginary component

StudentSubjectAssignmentScoreMax_Score
AliceMathQuiz_192100
BobScienceLab_Report85100
=IMAGINARY(COMPLEX(D2,E2))

Result: 100

IMAGINARY extracts the imaginary part (100) from the complex number 92+100i created by COMPLEX. This encoding stores the student's actual score as the real part and maximum possible score as the imaginary part, allowing two related metrics to be managed as a single value.

2. Parse score data from text-formatted complex number

StudentSubjectPerformance_Data
AliceMath88+100i
BobScience79+100i
=IMAGINARY("88+100i")

Result: 100

When complex numbers are stored or imported as text strings (e.g., "88+100i" representing a midterm score of 88 out of 100), IMAGINARY parses the string and extracts just the imaginary coefficient without requiring string manipulation functions. This is useful for importing gradebook data from external systems.

3. Reference complex number stored in a cell

StudentSubjectAssignmentScoreMax_ScoreEncoded_Grade
AliceMathQuiz_19210092+100i
BobScienceLab8510085+100i
=IMAGINARY(F2)

Result: 100

When a cell like F2 contains a complex number in text format ("92+100i"), IMAGINARY can reference that cell directly to extract the imaginary component. This approach decouples the extraction formula from the data source, making it easier to reuse across rows.

Common errors

Which IMAGINARY error are you seeing?
IMAGINARY returned an error#VALUE!
Provide inumber as either a text string matching "a+bi" format (e.g., "92+100i") or use COMPLEX(real_part, imaginary_part) to construct it from separate numeric cells.
#NUM!
Use more reasonable numbers or reduce decimal precision. Verify that you're not encoding exceptionally large scores or calculations that generate numbers beyond the engine's range.
#NAME?
Correct the spelling to IMAGINARY (nine letters: I-M-A-G-I-N-A-R-Y). Use the spreadsheet's autocomplete feature or double-check the formula bar.
ErrorWhy it happensHow to fix it
#VALUE!The inumber argument is not a valid complex number—for example, a plain text name like "Alice", a regular number like 100, or text that doesn't follow the "a+bi" format.Provide inumber as either a text string matching "a+bi" format (e.g., "92+100i") or use COMPLEX(real_part, imaginary_part) to construct it from separate numeric cells.
#NUM!The real or imaginary components contain values exceeding the spreadsheet's numeric limits—extremely large numbers, very small numbers, or precision beyond the system's floating-point capability.Use more reasonable numbers or reduce decimal precision. Verify that you're not encoding exceptionally large scores or calculations that generate numbers beyond the engine's range.
#NAME?The function name is misspelled in the formula, such as =IMAGIN(...), =IMAGINARY(...), or =IMAGINRY(...) instead of the correct spelling.Correct the spelling to IMAGINARY (nine letters: I-M-A-G-I-N-A-R-Y). Use the spreadsheet's autocomplete feature or double-check the formula bar.

Tips and when to use something else

  • IMAGINARY is specialized; most spreadsheets never use it. Reach for IMAGINARY only when you deliberately encode two related values into a single complex number for storage, transmission, or mathematical operations.
  • Always pair IMAGINARY with either COMPLEX(real, imaginary) to construct the number or supply it as a text string in "a+bi" format—passing a plain number returns #VALUE!.
  • Use IMREAL to extract the real (first) coefficient and IMAGINARY to extract the imaginary (second) coefficient. They're complementary functions in the complex-number family.
  • If you need to split encoded scores later, document the encoding scheme clearly (e.g., "real=actual_score, imaginary=max_score") so other users understand the format.

Related functions

COMPLEXCombines real and imaginary numbers into a complex number text string, essential for mathematical and engineering computation.IMREALExtracts the real coefficient of a complex number specified as a text string in the form a+bi or returned from the COMPLEX function.IFThe IF function returns one value if a condition is true and another (or nothing) if it is false, letting you branch logic in a cell.IFERRORIFERROR returns the result of a formula unless that formula generates an error, in which case it returns a user-specified alternative value.

Frequently asked questions

What's the difference between IMAGINARY and IMREAL?
IMREAL extracts the real (first) coefficient of a complex number (the "a" in a+bi), while IMAGINARY extracts the imaginary (second) coefficient (the "b" in a+bi). Use IMREAL for one metric and IMAGINARY for the other when decoding encoded pairs.
Can I use IMAGINARY on a regular number like 100?
No—IMAGINARY expects a complex number format. Passing a plain number returns #VALUE!. To extract 0 as an imaginary component, use IMAGINARY(COMPLEX(100,0)), which returns 0.
How do I create a complex number to pass to IMAGINARY?
Use COMPLEX(real_part, imaginary_part), such as COMPLEX(92,100), or type it as a text string like "92+100i". IMAGINARY accepts both and returns the imaginary coefficient.
Why would a typical spreadsheet user need IMAGINARY?
Primarily in engineering, physics, finance, or mathematics when working with complex mathematical calculations or when importing data systems that use complex number notation. Most daily spreadsheet tasks do not require it.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17