Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Can we create a formula using a cell that has an IF statement in it?

edited 12/09/19 in Archived 2015 Posts
05/29/15 Edited 12/09/19

I have a pretty long IF statement in a cell and want to use the results from that cell in a formula for another cell. The formula I am trying to create is VERY simple; 2 cells multiplied together. But I keep getting #INVALID. The only thing I can come up with is that it's not allowed to use the information that is generated in the IF cell. Anyone know?

 

Much thanks! 

 

SS Question Screen Shot.jpg

Tags:

Comments

  • TravisTravis Employee

    You can use the results of a formula in another formula. There is probably a mistake in your formula giving you that error.

     

    #INVALID DATA TYPE means the formula contains an unexpected ']' character. Try removing any extra ']' characters to fix it. If you are unable to figure it out, show us the formula and we will take a look! 

  • JamesRJamesR ✭✭✭✭

    The second formulae will error if the first formula returns an error code.  Additionally of you creae a formula in a date cell an it does not return a date recognise value it will not work.

  • The formula is:  =[Total LF]2 * [CND Cost per LF]2

     

    There are no errors in either of the referenced cells. The Total LF cell has "2400" and the CND Cost per LF is the cell with the long IF statement and it returns "0.74" on this particular row. So, no error there either. 

     

    Your help is GREATLY appreciated!

  • JamesRJamesR ✭✭✭✭

    Is 2400 a number?  What I mean by this is if you have pulled the number out of a string using one of the text functions then it will return as text and you will not be able to calculate from if.

    What type of field is the final calculation in.  If its a Date field you can have problems or if its a Symbol column also.

  • 2400 is a number in a Text/Number field and the Cost Total is also a Text/Number field. I double checked just to be sure.

     

    Gotta say, I'm glad this isn't just a quick fix. Makes me feel a little better about not figuring it out on my own! LOL

     

    Next....

  • JamesRJamesR ✭✭✭✭

    Quick Check;

     

    in an empty text number cell type an = sign then click on one of the cells that has the result of a formula.  Does the result appear in the cell?  Do this with both cells involved.

     

    If both results appear then look at your formulae and check that you have the nae exactly right, spaces, underscores etc.

     

    Failing that contact my company and we will look into seeing how we might help (view my Profile).

  • The = worked, I then tried to do the formula using the new column and it says it a #Invalid data typeCry

     

    Thanks

  • TravisTravis Employee

    Bobko shared the sheet with me to investigate and we were able to figure out the cause of the error. The formula located in [CND Cost per LF]2 had quotes around the resulting numbers, which converted the formula result to text values (Which is what JamesR was suggesting). Removing the quotes around the resulting numbers, kept the formula result formatted as a number and now there is no longer an error message. 

     

    The original formula (0.74 as text):

     

    =IF(Depth2 = "9 1/2", "0.74"

     

    New formula (0.74 as a number):

     

    =IF(Depth2 = "9 1/2", 0.74

     

    You can also use the VALUE function to convert a text value to a number. Here is an example:

     

     

    =[Total LF]2 * VALUE([CND Cost per LF]2)

  • Hannah CHannah C ✭✭✭✭✭

    This helped me with the same issue! Thank you so much!

    -Hannah

  • edited 02/13/19

    Hi. I have the same issue, but when I add the VALUE function, I still get the #INVALID OPERATION error.

     

    I want to multiply two cells together:

    =[Percentage]51*[Percent Completed]51

    The resulting value of the cells above is = 2%*100%

     

    FORMULA FOR [Percentage]51:

     =IF([Story Points]51 > 0, [Story Points]51 / [Story Points ]$256)

     

    FORMULA FOR [Percent Completed ]51:

    =IF(Status51 = "Assigned - In Progress", "25%", IF(Status51 = "Ready to Test", "50%", IF(Status51 = "Tested - Need Some Changes", "75%", IF(Status51 = "Completed", "100%", "0%"))))

     

    Thanks!

     

This discussion has been closed.