对照表参考比较两个总数以确定指标

你好,

我正在研究一个公式,在这里我引用一个表与数字设置在另一个指标,以确定是否有东西是红色,黄色或绿色。

我在我的参考表上使用一个称为差异的单元格。代码应该是这样的:如果小于200000.00,它应该是红色的,大于100000.00应该是绿色的,或者应该是黄色的

我尝试了一些基本的东西,只是想看看我是否能让指示器工作,但没有

=IF({Difference}, @cell < " 20000.00 ",红色,IF({Difference}, @cell >= " 1000000.00 ",绿色,黄色))

=IF({Difference}, @cell >= "$ 200000.00 ",红色,IF({Difference}, @cell >= "$ 100000.00 ",绿色,黄色))

=IF({Difference}, @cell < " 20000 ",红色,IF({Difference}, @cell >= "100000",绿色,黄色))

我实际上是在参考表上引用单个单元格。任何帮助都将不胜感激。

image.png


我应该在Row使用吗?

卡洛琳

最好的答案

  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭
    ✓回答

    在公式中对数字加引号会将其更改为文本字符串(不能小于或大于数字)。


    试着去掉数字上的引号,把它们放在颜色周围。


    我还注意到你的报价是倾斜的。除非你把它们“斜体化”,否则这些都是错误的引号类型。

    它们被称为“智能引号”(具有讽刺意味的是),在Smartsheet公式中不是有效字符。你会想要在这里重新键入它们,直接在Smartsheet中,或者在记事本等文本编辑器中(不是Word)。您将看到在前面提到的地方键入它们会使它们直接上下移动。这些是你要找的报价。

    thinkspi.com

  • 吉纳维芙P。
    吉纳维芙P。 员工管理
    ✓回答

    @delaurellc

    你就快成功了!希望你不介意我插嘴@Paul新来的

    对于IF语句,它的第一部分是逻辑语句,然后加上逗号。现在你列出了一个范围,然后是逗号,所以你的逻辑很混乱。

    只要{reference}只指向单个单元格,就可以直接与数字进行比较,而不用使用@cell

    试一试:

    =IF({Difference} < 20000, "Red", IF({Difference} >= 100000, "Green", "Yellow"))


    欢呼,

    吉纳维芙

答案

  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭
    ✓回答

    在公式中对数字加引号会将其更改为文本字符串(不能小于或大于数字)。


    试着去掉数字上的引号,把它们放在颜色周围。


    我还注意到你的报价是倾斜的。除非你把它们“斜体化”,否则这些都是错误的引号类型。

    它们被称为“智能引号”(具有讽刺意味的是),在Smartsheet公式中不是有效字符。你会想要在这里重新键入它们,直接在Smartsheet中,或者在记事本等文本编辑器中(不是Word)。您将看到在前面提到的地方键入它们会使它们直接上下移动。这些是你要找的报价。

    thinkspi.com

  • 嗨,保罗,

    你现在一定厌倦我了吧,照你说的做了,还是没用

    =IF({Difference}, @cell < 20000, "红色",IF({Difference}, @cell >= 100000, "绿色","黄色"))

    错误是得到不正确的参数集

  • 吉纳维芙P。
    吉纳维芙P。 员工管理
    ✓回答

    @delaurellc

    你就快成功了!希望你不介意我插嘴@Paul新来的

    对于IF语句,它的第一部分是逻辑语句,然后加上逗号。现在你列出了一个范围,然后是逗号,所以你的逻辑很混乱。

    只要{reference}只指向单个单元格,就可以直接与数字进行比较,而不用使用@cell

    试一试:

    =IF({Difference} < 20000, "Red", IF({Difference} >= 100000, "Green", "Yellow"))


    欢呼,

    吉纳维芙

  • 吉纳维芙,成功了。谢谢你!几分钟后我还有一个问题。我添加到一个字符串本身,所以如果其他人有问题,他们可以搜索它,并得到答案!

    你和保罗太棒了!

    我学到了很多东西!

  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭

    @Genevieve P。一点也不我说了引号的类型和位置,甚至没有注意到逗号。

    thinkspi.com

帮助文章参考资料欧宝体育app官方888

想要直接在智能表中练习使用公式吗?

请查看公式手册模板!
You da man, @Paul Newcome<\/a>! That works perfectly. Thank you!!!<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":106782,"type":"question","name":"IF cell contains specific letter then return value","excerpt":"I'd like to return a value of 120\/208 in Service Volts for Xfmr# ending in E value of 277\/480 for Xfmr# ending in X value of 120\/240 for Xfmr# ending with no letter Thanks","categoryID":322,"dateInserted":"2023-06-22T16:38:09+00:00","dateUpdated":"2023-06-22T16:45:09+00:00","dateLastComment":"2023-06-22T18:12:51+00:00","insertUserID":158055,"insertUser":{"userID":158055,"name":"mromaire","url":"https:\/\/community.smartsheet.com\/profile\/mromaire","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-22T18:12:03+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"lastUserID":158055,"lastUser":{"userID":158055,"name":"mromaire","url":"https:\/\/community.smartsheet.com\/profile\/mromaire","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-22T18:12:03+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":35,"score":null,"hot":3374911260,"url":"https:\/\/community.smartsheet.com\/discussion\/106782\/if-cell-contains-specific-letter-then-return-value","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106782\/if-cell-contains-specific-letter-then-return-value","format":"Rich","lastPost":{"discussionID":106782,"commentID":381817,"name":"Re: IF cell contains specific letter then return value","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381817#Comment_381817","dateInserted":"2023-06-22T18:12:51+00:00","insertUserID":158055,"insertUser":{"userID":158055,"name":"mromaire","url":"https:\/\/community.smartsheet.com\/profile\/mromaire","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-22T18:12:03+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/RLY9EZQ8E5Y9\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-22T18:08:50+00:00","dateAnswered":"2023-06-22T17:35:06+00:00","acceptedAnswers":[{"commentID":381799,"body":"

Hi @mromaire<\/a>,<\/p>

Try this!<\/p>

=IF(RIGHT([xfmr#]@row, 1) = \"X\", \"277\/480\", IF(RIGHT([xfmr#]@row, 1) = \"E\", \"120\/208\", \"120\/240\"))<\/p>

Hope that helps!<\/p>

BRgds,<\/p>

-Ray<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":106771,"type":"question","name":"SUMIFS Formula for Column with Various Possible Returns","excerpt":"Hi there! I am a big Excel user switching to Smartsheet and need help with a SUMIFS formula. In Excel, the formula was: =SUM(SUMIFS('Outside Counsel Invoices'!$G:$G,'Outside Counsel Invoices'!$M:$M,\"FY23\",'Outside Counsel Invoices'!$D:$D,{\"=7607\",\"=7607*\"})) Since Wildcards (*) are not used in Smartsheet, I am struggling…","categoryID":322,"dateInserted":"2023-06-22T14:38:12+00:00","dateUpdated":null,"dateLastComment":"2023-06-22T18:57:32+00:00","insertUserID":162633,"insertUser":{"userID":162633,"name":"BuckeyeGirl72","url":"https:\/\/community.smartsheet.com\/profile\/BuckeyeGirl72","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-22T19:01:20+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-22T19:34:53+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":32,"score":null,"hot":3374907944,"url":"https:\/\/community.smartsheet.com\/discussion\/106771\/sumifs-formula-for-column-with-various-possible-returns","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106771\/sumifs-formula-for-column-with-various-possible-returns","format":"Rich","lastPost":{"discussionID":106771,"commentID":381826,"name":"Re: SUMIFS Formula for Column with Various Possible Returns","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381826#Comment_381826","dateInserted":"2023-06-22T18:57:32+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-22T19:34:53+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-22T18:38:09+00:00","dateAnswered":"2023-06-22T17:53:03+00:00","acceptedAnswers":[{"commentID":381809,"body":"

It looks like you have a mix of text strings and numerical values. Try the below. If that doesn't work then we do have one more option.<\/p>

SUMIFS({Range To Sum}, {Range To Evaluate}, OR(@cell = 7607, CONTAINS(\"7607\", @cell)))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

公式和函数趋势