[Excel] Excel cell formula is too long how to set

Source: Internet
Author: User
Problem:
Input the if nested function in the cell of Excel. Because there are too many options, the IF statement is too long. data cannot be copied in the value field using "Data Validity-sequence ), so the problem arises: The formula settings in the Excel cell prompt is too long. What should I do? The formula has not been edited yet.

Explanation:
The formula content is 1,024 characters in length. If your formula does not exceed 1024 characters, you will be prompted that the formula is too long. This is usually because the formula contains missing or contains multiple input parentheses, commas, and so on.

Solution:
If the formula does exceed 1024 characters. You can reduce the number of characters in the formula by defining the name.

Example:
For example, name management, where: New "name defines x = offset (sheet1! $ A $1, counta (sheet1! $ A: $ A), counta (sheet1! $1: $1 ))
After the name is defined, the offset () part of the long formula can be replaced by X, greatly reducing the length of the formula. For example, the original formula is = index (offset (...), row (), column ())
After defining the name, you can change it to = index (x, row (), column ())

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.