将"= IF(ISNUMBER(SEARCH')"答案转换为数字而不是文本(以使用自动求和) [英] Turn '=IF(ISNUMBER(SEARCH')' answer into numeric instead of text (to use autosum)

问题描述

如果一个单元格包含某个快递公司(DHL，DPD等)，我想让Excel计算出来，我希望它给出到最近的下车地点的英里数.它有效，但是它给了我一个文本答案，而不是一个数字，这意味着autosum将不起作用.如何更改代码以给出数字答案?干杯布莱恩

I'm trying to get Excel to work out if a cell contains a certain courier company (DHL, DPD etc) and I want it to give out the number of miles to the nearest drop-off point. It works but it gives me a text answer instead of a number, this means autosum won't work. How do I change the code to give a numeric answer? Cheers Brian

    =IF(
        ISNUMBER(SEARCH("collect",Q2,1))=TRUE,"1", 
        IF(ISNUMBER(SEARCH("dhl",Q2,1))=TRUE,"2.6", 
        IF(ISNUMBER(SEARCH("DPD",Q2,1))=TRUE,"1", 
        IF(ISNUMBER(SEARCH("inpost",Q2,1))=TRUE,"1.2", 
        IF(ISNUMBER(SEARCH("hermes",Q2,1))=TRUE,"0.6",  
        IF(ISNUMBER(SEARCH("royal",Q2,1))=TRUE,"0.5", 
        IF(ISNUMBER(SEARCH("force",Q2,1))=TRUE,"0.5", 
        IF(ISNUMBER(SEARCH("ups",Q2,1))=TRUE,"0.6",  
        IF(ISNUMBER(SEARCH("yodel",Q2,1))=TRUE,"0", 
        IF(ISNUMBER(SEARCH("collection",Q2,1))=TRUE,"0",
    "No"))

推荐答案

我只是按照注释复制了您的公式，并删除了=TRUE:

I just copied your formula in accordance with the comments, and removed =TRUE:

=IF
  (ISNUMBER(SEARCH("collection",Q2,1)),1, 
  IF(ISNUMBER(SEARCH("dhl",Q2,1)),2.6, 
  IF(ISNUMBER(SEARCH("DPD",Q2,1)),1, 
  IF(ISNUMBER(SEARCH("inpost",Q2,1)),1.2, 
  IF(ISNUMBER(SEARCH("hermes",Q2,1)),0.6,  
  IF(ISNUMBER(SEARCH("royal",Q2,1)),0.5, 
  IF(ISNUMBER(SEARCH("force",Q2,1)),0.5, 
  IF(ISNUMBER(SEARCH("ups",Q2,1)),0.6,  
  IF(ISNUMBER(SEARCH("yodel",Q2,1)),0, 
0))))))))

这篇关于将"= IF(ISNUMBER(SEARCH')"答案转换为数字而不是文本(以使用自动求和)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！