SOLVED

Formula help needed..

%3CLINGO-SUB%20id%3D%22lingo-sub-2183814%22%20slang%3D%22en-US%22%3EFormula%20help%20needed..%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2183814%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%20Im%20a%20little%20bit%20stuck%20here%20with%20this%20nested%20formula%20that%20doesn%20work%20for%20me.%26nbsp%3B%20Here%20is%20what%20i%20am%20trying%20to%20do%20here%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIF%20A%3Dtext1%20then%20(B-C)*10%20and%20IF%20A%3Dtext2%20then%20(C-B)*10%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20helpm%20me%20please%3F%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2183814%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2183860%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%20needed..%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2183860%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F986025%22%20target%3D%22_blank%22%3E%404lvis_bondarenko%3C%2FA%3E%26nbsp%3BTry%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(A1%3D%22Text%201%22%2C(B1-C1)*10%2CIF(A1%3D%22Text%202%22%2C(C1-B1)*10%2C%22%22))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello! Im a little bit stuck here with this nested formula that doesn work for me.  Here is what i am trying to do here: 

 

IF A=text1 then (B-C)*10 and IF A=text2 then (C-B)*10

 

Can anyone helpm me please?

Thanks in advance.

 

6 Replies

@4lvis_bondarenko Try this:

=IF(A1="Text 1",(B1-C1)*10,IF(A1="Text 2",(C1-B1)*10,""))

 

Nope, doesnt work mate

@4lvis_bondarenko Perhaps your local settings require a semi colon to separate the arguments in a formula. The attached workbook contains a working example, that will automatically adjust to your local requirements. Try.

Screenshot 2021-03-04 at 06.39.56.png

@Riny_van_Eekelen This is the error that i get and error trace tool show where the fault is but i dont understand whats wrong with it.

4lvis_bondarenko_0-1614872461824.png

 

best response confirmed by 4lvis_bondarenko (Occasional Contributor)
Solution

@4lvis_bondarenko 

Instead of I:I please use I2:I10000, even better dynamic ranges, even more better transform range to table.

Same for each column in formula.

Sorted. Thanks a lot mate.