|
楼主 |
发表于 2023-2-25 16:04:31
|
显示全部楼层
大于1的公式,目前10的N次方没有弄,=IF(AND(IFERROR(FIND(REPT("9",C2),(A2/POWER(10,INT(LOG(A2))+1)),1),1)=3,(A2/POWER(10,INT(LOG(A2))+1))*1<("0."&REPT("9",C2)&"5")*1),("0."&REPT("9",C2)),IF(AND(IFERROR(FIND(REPT("9",C2),(A2/POWER(10,INT(LOG(A2))+1))*1,1),1)=3,OR((A2/POWER(10,INT(LOG(A2))+1))*1>("0."&REPT("9",C2)&"5")*1,(A2/POWER(10,INT(LOG(A2))+1))*1=("0."&REPT("9",C2)&"5")*1)),("1."&REPT("0",C2-1)),IF(IFERROR(FIND(REPT("9",C2),(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1))&RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1))),1),1)=3,IF((LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1))&RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)))*1<("0."&REPT("9",C2)&"5")*1,("0."&REPT("0",INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1))))))&REPT("9",C2)),("0." & REPT("0",INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-1)&"1"& REPT("0",C2-1))),IF(OR(LEFT(RIGHT(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)),LEN(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)))-C2-2),1)*1<5,AND(5&REPT("0",LEN(RIGHT(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)),LEN(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)))-C2-2))-1)=RIGHT(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)),LEN(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)))-C2-2),ISEVEN(RIGHT(LEFT(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)),C2+2),1)*1))),ROUNDDOWN(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)),C2),ROUNDUP(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)),C2))/POWER(10,INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))) & REPT("0",C2+INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-IF(ROUNDUP(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)),C2)=1,1,INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-2)-LEN(IF(OR(LEFT(RIGHT(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)),LEN(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)))-C2-2),1)*1<5,AND(5&REPT("0",LEN(RIGHT(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)),LEN(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)))-C2-2))-1)=RIGHT(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)),LEN(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)))-C2-2),ISEVEN(RIGHT(LEFT(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)),C2+2),1)*1))),ROUNDDOWN(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)),C2),ROUNDUP(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)),C2)))))))*POWER(10,INT(LOG(A2))+1) |
|