[Bug] formula if issue #3772

zhaoziji · 2024-10-16T10:16:54Z

Before you submit this issue, have you checked the following

Is this really a problem?
I have searched the Github Issues for similar issues, but did not find anything.

Affected packages and versions

0.3.0

Reproduction link

I don't know how to use the online template, so I provide the workbook data.

{
    "id": "XGBQ9r",
    "sheetOrder": ["esfePQ9lBZ68_24tVAVzo"],
    "name": "",
    "appVersion": "0.3.0",
    "locale": "zhCN",
    "styles": {
        "2rWMxl": {"bg": {"rgb": "#EFFBD0"}},
        "0B_XQP": {
            "ff": "Arial",
            "fs": 11,
            "it": 0,
            "bl": 0,
            "ul": {"s": 0},
            "st": {"s": 0},
            "ol": {"s": 0},
            "tr": {"a": 0, "v": 0},
            "td": 0,
            "bg": {"rgb": "#EFFBD0"},
            "ht": 0,
            "vt": 0,
            "tb": 0,
            "pd": {"t": 0, "b": 2, "l": 2, "r": 2},
            "cl": null,
            "n": null
        },
        "xNReZo": {
            "ff": "Arial",
            "fs": 11,
            "it": 0,
            "bl": 0,
            "ul": {"s": 0},
            "st": {"s": 0},
            "ol": {"s": 0},
            "tr": {"a": 0, "v": 0},
            "td": 0,
            "ht": 0,
            "vt": 0,
            "tb": 0,
            "pd": {"t": 0, "b": 2, "l": 2, "r": 2},
            "cl": null,
            "bg": null,
            "n": null
        }
    },
    "sheets": {
        "esfePQ9lBZ68_24tVAVzo": {
            "id": "esfePQ9lBZ68_24tVAVzo",
            "name": "Sheet1",
            "tabColor": "",
            "hidden": 0,
            "rowCount": 1000,
            "columnCount": 20,
            "zoomRatio": 1,
            "freeze": {"xSplit": 0, "ySplit": 0, "startRow": -1, "startColumn": -1},
            "scrollTop": 0,
            "scrollLeft": 0,
            "defaultColumnWidth": 88,
            "defaultRowHeight": 24,
            "mergeData": [],
            "cellData": {
                "6": {"2": {"v": "异常", "t": 1}, "4": {"v": "正常", "t": 1}},
                "7": {"2": {"s": "2rWMxl", "v": 1, "t": 2}, "4": {"v": 1, "t": 2, "s": "0B_XQP"}},
                "8": {"4": {"s": "xNReZo"}},
                "9": {"4": {"s": "xNReZo"}},
                "10": {"4": {"s": "xNReZo"}},
                "11": {"4": {"s": "xNReZo"}},
                "12": {"4": {"s": "xNReZo"}},
                "13": {"4": {"s": "xNReZo"}},
                "14": {"4": {"s": "xNReZo"}},
                "15": {"4": {"s": "xNReZo"}},
                "16": {"4": {"s": "xNReZo"}},
                "17": {"4": {"s": "xNReZo"}},
                "18": {"4": {"s": "xNReZo"}},
                "19": {"4": {"s": "xNReZo"}},
                "20": {"4": {"s": "xNReZo"}},
                "21": {"4": {"s": "xNReZo"}},
                "22": {"4": {"s": "xNReZo"}},
                "23": {"4": {"s": "xNReZo"}},
                "24": {"4": {"s": "xNReZo"}},
                "25": {"4": {"s": "xNReZo"}},
                "26": {"4": {"s": "xNReZo"}},
                "27": {"4": {"s": "xNReZo"}},
                "28": {"4": {"s": "xNReZo"}},
                "29": {
                    "2": {"f": "=IF(C8<0,IF(C31<1,1,0.8),IF(C31<1,0.95,1))", "v": 1, "t": 2},
                    "4": {
                        "s": "xNReZo",
                        "f": "=IF(E8<0,IF(E31<1,1,0.8),IF(E31<1,0.95,1))",
                        "si": "EDVgVn",
                        "v": 0.95,
                        "t": 2
                    }
                },
                "30": {"2": {"f": "=C32/100", "v": 0.01, "t": 2}, "4": {"s": "0B_XQP", "v": 0.01, "t": 2}},
                "31": {"2": {"s": "2rWMxl", "v": 1, "t": 2}, "4": {"s": "xNReZo"}}
            },
            "rowData": {
                "6": {"hd": 0, "h": 24, "ah": 24},
                "7": {"hd": 0, "h": 24, "ah": 24},
                "8": {"hd": 0, "h": 24},
                "9": {"hd": 0, "h": 24},
                "10": {"hd": 0, "h": 24},
                "11": {"hd": 0, "h": 24},
                "12": {"hd": 0, "h": 24},
                "13": {"hd": 0, "h": 24},
                "14": {"hd": 0, "h": 24},
                "15": {"hd": 0, "h": 24},
                "16": {"hd": 0, "h": 24},
                "17": {"hd": 0, "h": 24},
                "18": {"hd": 0, "h": 24},
                "19": {"hd": 0, "h": 24},
                "20": {"hd": 0, "h": 24},
                "21": {"hd": 0, "h": 24},
                "22": {"hd": 0, "h": 24},
                "23": {"hd": 0, "h": 24},
                "24": {"hd": 0, "h": 24},
                "25": {"hd": 0, "h": 24},
                "26": {"hd": 0, "h": 24},
                "27": {"hd": 0, "h": 24},
                "28": {"hd": 0, "h": 24},
                "29": {"hd": 0, "h": 24, "ah": 24},
                "30": {"hd": 0, "h": 24, "ah": 24},
                "31": {"hd": 0, "h": 24, "ah": 24}
            },
            "columnData": {"2": {"w": 88, "hd": 0}, "4": {"w": 88, "hd": 0}, "5": {"w": 88, "hd": 0}},
            "showGridlines": 1,
            "rowHeader": {"width": 46, "hidden": 0},
            "columnHeader": {"height": 20, "hidden": 0},
            "rightToLeft": 0
        }
    },
    "resources": [{"name": "SHEET_RANGE_PROTECTION_PLUGIN", "data": "{}"}, {
        "name": "SHEET_WORKSHEET_PROTECTION_PLUGIN",
        "data": "{}"
    }, {"name": "SHEET_WORKSHEET_PROTECTION_POINT_PLUGIN", "data": "{}"}, {
        "name": "SHEET_DEFINED_NAME_PLUGIN",
        "data": "{}"
    }, {"name": "SHEET_DATA_VALIDATION_PLUGIN", "data": "{}"}, {
        "name": "SHEET_AuthzIoMockService_PLUGIN",
        "data": "{}"
    }]
}

Expected behavior

formula if(..., if(...), if(...)) can work without any issue.

Actual behavior

try to reset the C8 using value "1"， we can get the correct result such as "the value of C30 is 0.95";
try to reset the C32 using value "1", we can get the incorrect result such as "the value of C30 is 1";

and on the other hand,
try to reset the E8 using value "1", or reset the E31 using value 0.01, we can always get the correct result such as "the value of E30 is 0.95".

System information

No response

univer-bot · 2024-10-16T10:16:59Z

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

Origin Title: [Bug] formula if issue

Title: [Bug] formula if issue

Before you submit this issue, have you checked the following

Is this really a problem?
I have searched the Github Issues for similar issues, but did not find anything.

Affected packages and versions

0.3.0

Reproduction link

I don't know how to use the online template, so I provide the workbook data.

{
    "id": "XGBQ9r",
    "sheetOrder": ["esfePQ9lBZ68_24tVAVzo"],
    "name": "",
    "appVersion": "0.3.0",
    "locale": "zhCN",
    "styles": {
        "2rWMxl": {"bg": {"rgb": "#EFFBD0"}},
        "0B_XQP": {
            "ff": "Arial",
            "fs": 11,
            "it": 0,
            "bl": 0,
            "ul": {"s": 0},
            "st": {"s": 0},
            "ol": {"s": 0},
            "tr": {"a": 0, "v": 0},
            "td": 0,
            "bg": {"rgb": "#EFFBD0"},
            "ht": 0,
            "vt": 0,
            "tb": 0,
            "pd": {"t": 0, "b": 2, "l": 2, "r": 2},
            "cl": null,
            "n": null
        },
        "xNReZo": {
            "ff": "Arial",
            "fs": 11,
            "it": 0,
            "bl": 0,
            "ul": {"s": 0},
            "st": {"s": 0},
            "ol": {"s": 0},
            "tr": {"a": 0, "v": 0},
            "td": 0,
            "ht": 0,
            "vt": 0,
            "tb": 0,
            "pd": {"t": 0, "b": 2, "l": 2, "r": 2},
            "cl": null,
            "bg": null,
            "n": null
        }
    },
    "sheets": {
        "esfePQ9lBZ68_24tVAVzo": {
            "id": "esfePQ9lBZ68_24tVAVzo",
            "name": "Sheet1",
            "tabColor": "",
            "hidden": 0,
            "rowCount": 1000,
            "columnCount": 20,
            "zoomRatio": 1,
            "freeze": {"xSplit": 0, "ySplit": 0, "startRow": -1, "startColumn": -1},
            "scrollTop": 0,
            "scrollLeft": 0,
            "defaultColumnWidth": 88,
            "defaultRowHeight": 24,
            "mergeData": [],
            "cellData": {
                "6": {"2": {"v": "异常", "t": 1}, "4": {"v": "正常", "t": 1}},
                "7": {"2": {"s": "2rWMxl", "v": 1, "t": 2}, "4": {"v": 1, "t": 2, "s": "0B_XQP"}},
                "8": {"4": {"s": "xNReZo"}},
                "9": {"4": {"s": "xNReZo"}},
                "10": {"4": {"s": "xNReZo"}},
                "11": {"4": {"s": "xNReZo"}},
                "12": {"4": {"s": "xNReZo"}},
                "13": {"4": {"s": "xNReZo"}},
                "14": {"4": {"s": "xNReZo"}},
                "15": {"4": {"s": "xNReZo"}},
                "16": {"4": {"s": "xNReZo"}},
                "17": {"4": {"s": "xNReZo"}},
                "18": {"4": {"s": "xNReZo"}},
                "19": {"4": {"s": "xNReZo"}},
                "20": {"4": {"s": "xNReZo"}},
                "21": {"4": {"s": "xNReZo"}},
                "22": {"4": {"s": "xNReZo"}},
                "23": {"4": {"s": "xNReZo"}},
                "24": {"4": {"s": "xNReZo"}},
                "25": {"4": {"s": "xNReZo"}},
                "26": {"4": {"s": "xNReZo"}},
                "27": {"4": {"s": "xNReZo"}},
                "28": {"4": {"s": "xNReZo"}},
                "29": {
                    "2": {"f": "=IF(C8<0,IF(C31<1,1,0.8),IF(C31<1,0.95,1))", "v": 1, "t": 2},
                    "4": {
                        "s": "xNReZo",
                        "f": "=IF(E8<0,IF(E31<1,1,0.8),IF(E31<1,0.95,1))",
                        "si": "EDVgVn",
                        "v": 0.95,
                        "t": 2
                    }
                },
                "30": {"2": {"f": "=C32/100", "v": 0.01, "t": 2}, "4": {"s": "0B_XQP", "v": 0.01, "t": 2}},
                "31": {"2": {"s": "2rWMxl", "v": 1, "t": 2}, "4": {"s": "xNReZo"}}
            },
            "rowData": {
                "6": {"hd": 0, "h": 24, "ah": 24},
                "7": {"hd": 0, "h": 24, "ah": 24},
                "8": {"hd": 0, "h": 24},
                "9": {"hd": 0, "h": 24},
                "10": {"hd": 0, "h": 24},
                "11": {"hd": 0, "h": 24},
                "12": {"hd": 0, "h": 24},
                "13": {"hd": 0, "h": 24},
                "14": {"hd": 0, "h": 24},
                "15": {"hd": 0, "h": 24},
                "16": {"hd": 0, "h": 24},
                "17": {"hd": 0, "h": 24},
                "18": {"hd": 0, "h": 24},
                "19": {"hd": 0, "h": 24},
                "20": {"hd": 0, "h": 24},
                "21": {"hd": 0, "h": 24},
                "22": {"hd": 0, "h": 24},
                "23": {"hd": 0, "h": 24},
                "24": {"hd": 0, "h": 24},
                "25": {"hd": 0, "h": 24},
                "26": {"hd": 0, "h": 24},
                "27": {"hd": 0, "h": 24},
                "28": {"hd": 0, "h": 24},
                "29": {"hd": 0, "h": 24, "ah": 24},
                "30": {"hd": 0, "h": 24, "ah": 24},
                "31": {"hd": 0, "h": 24, "ah": 24}
            },
            "columnData": {"2": {"w": 88, "hd": 0}, "4": {"w": 88, "hd": 0}, "5": {"w": 88, "hd": 0}},
            "showGridlines": 1,
            "rowHeader": {"width": 46, "hidden": 0},
            "columnHeader": {"height": 20, "hidden": 0},
            "rightToLeft": 0
        }
    },
    "resources": [{"name": "SHEET_RANGE_PROTECTION_PLUGIN", "data": "{}"}, {
        "name": "SHEET_WORKSHEET_PROTECTION_PLUGIN",
        "data": "{}"
    }, {"name": "SHEET_WORKSHEET_PROTECTION_POINT_PLUGIN", "data": "{}"}, {
        "name": "SHEET_DEFINED_NAME_PLUGIN",
        "data": "{}"
    }, {"name": "SHEET_DATA_VALIDATION_PLUGIN", "data": "{}"}, {
        "name": "SHEET_AuthzIoMockService_PLUGIN",
        "data": "{}"
    }]
}

Expected behavior

formula if(..., if(...), if(...)) can work without any issue.

Actual behavior

try to reset the C8 using value "1"， we can get the correct result such as "the value of C30 is 0.95";
try to reset the C32 using value "1", we can get the incorrect result such as "the value of C30 is 1";

and on the other hand,
try to reset the E8 using value "1", or reset the E31 using value 0.01, we can always get the correct result such as "the value of E30 is 0.95".

System information

No response

zhaoziji · 2024-10-18T09:01:49Z

this issue is still existing!!!

zhaoziji · 2024-10-18T09:26:59Z

pls try this example
https://github.com/zhaoziji/sheets-vite-demo

wpxp123456 · 2024-10-21T06:18:03Z

@zhaoziji If you have time, please help us check if there are any other cases with problems. The corresponding pull request has a preview link

zhaoziji · 2024-10-21T13:44:20Z

@zhaoziji If you have time, please help us check if there are any other cases with problems. The corresponding pull request has a preview link

it is my pleasure. I wanna confirm with you that, the preview link is https://univer-mnpyv8k6z-univer.vercel.app, all right ?

seems that I can't open this link. is it possible to get the preview version by "npm install" command?

wpxp123456 · 2024-10-22T02:33:27Z

https://univer-d7czcjomz-univer.vercel.app/sheets/

wpxp123456 · 2024-10-22T02:34:02Z

zhaoziji · 2024-10-22T02:52:24Z

https://univer-d7czcjomz-univer.vercel.app/sheets/

got it, I will check it soon.

zhaoziji · 2024-10-22T03:14:18Z

it works correctly.

wpxp123456 · 2024-10-22T03:38:02Z

ok, will be released with the next version

zhaoziji · 2024-10-24T07:28:01Z

i found another issue as the attachment

univer-bot · 2024-10-24T07:28:04Z

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

i found another issue as the attachment

zhaoziji · 2024-10-24T07:28:08Z

i found another issue as the attachment

univer-bot · 2024-10-24T07:28:11Z

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

i found another issue as the attachment

zhaoziji added the bug Something isn't working label Oct 16, 2024

wpxp123456 self-assigned this Oct 16, 2024

wpxp123456 mentioned this issue Oct 16, 2024

fix(formula): fix some bug #3774

Merged

4 tasks

wpxp123456 closed this as completed in #3774 Oct 17, 2024

jikkai reopened this Oct 18, 2024

wpxp123456 mentioned this issue Oct 19, 2024

fix(formula): fix CELL_INVERTED_INDEX_CACHE range set #3807

Merged

4 tasks

DR-Univer closed this as completed in #3807 Oct 22, 2024

wpxp123456 mentioned this issue Oct 25, 2024

fix(formula): fix sumif formula range and sumRange different dimensions #3853

Merged

4 tasks

dream-num deleted a comment from wpxp123456 Oct 25, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

[Bug] formula if issue #3772

[Bug] formula if issue #3772

zhaoziji commented Oct 16, 2024

univer-bot bot commented Oct 16, 2024

zhaoziji commented Oct 18, 2024

zhaoziji commented Oct 18, 2024

wpxp123456 commented Oct 21, 2024

zhaoziji commented Oct 21, 2024 •

edited

Loading

wpxp123456 commented Oct 22, 2024

wpxp123456 commented Oct 22, 2024

zhaoziji commented Oct 22, 2024

zhaoziji commented Oct 22, 2024

wpxp123456 commented Oct 22, 2024

zhaoziji commented Oct 24, 2024

univer-bot bot commented Oct 24, 2024

zhaoziji commented Oct 24, 2024

univer-bot bot commented Oct 24, 2024

[Bug] formula if issue #3772

[Bug] formula if issue #3772

Comments

zhaoziji commented Oct 16, 2024

Before you submit this issue, have you checked the following

Affected packages and versions

Reproduction link

Expected behavior

Actual behavior

System information

univer-bot bot commented Oct 16, 2024

Before you submit this issue, have you checked the following

Affected packages and versions

Reproduction link

Expected behavior

Actual behavior

System information

zhaoziji commented Oct 18, 2024

zhaoziji commented Oct 18, 2024

wpxp123456 commented Oct 21, 2024

zhaoziji commented Oct 21, 2024 • edited Loading

wpxp123456 commented Oct 22, 2024

wpxp123456 commented Oct 22, 2024

zhaoziji commented Oct 22, 2024

zhaoziji commented Oct 22, 2024

wpxp123456 commented Oct 22, 2024

zhaoziji commented Oct 24, 2024

univer-bot bot commented Oct 24, 2024

zhaoziji commented Oct 24, 2024

univer-bot bot commented Oct 24, 2024

zhaoziji commented Oct 21, 2024 •

edited

Loading