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

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

Find more posts tagged with

Formulas

Comments

Travis

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!

JamesR

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.

Bobko

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!

JamesR

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.

Bobko

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....

JamesR

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).

Bobko

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

Thanks

Travis

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 C

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

-Hannah

PageDNA

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!