Combining multiple if(functions in one cell

%3CLINGO-SUB%20id%3D%22lingo-sub-1598187%22%20slang%3D%22en-US%22%3ECombining%20multiple%20if(functions%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1598187%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20this%20one%20is%20a%20bit%20tricky.%20I'm%20trying%20to%20connect%20these%20three%20separate%20formulas%20into%20one%20cell%3A%26nbsp%3B%3CBR%20%2F%3EI%20got%20one%20formula%20which%20is%20-%20if%20this%20cell%20is%20salmon%20%2C%20and%20there%20is%201%2B%20of%20them%2C%20choose%20this%20cell%20with%20input%20length%20cm%20to%20calculate%20weight%2C%20if%20not%20pick%20the%20cell%20with%20height%20(n12)%20and%20use%20this%20formula.%3CBR%20%2F%3E%3CBR%20%2F%3E%3DIF(I12%3D%22salmon%22%2CIF(M12%26gt%3B0%2CSUM((((M12%2F100)%5E3)*10)*0.9711)%2CSUM(0.004*N12%5E2.535)))%20-%20Works%3CBR%20%2F%3E%3CBR%20%2F%3Ethen%20I%20got%20the%20same%20one%20for%20trout%20but%20without%20the%20height%20formula%3CBR%20%2F%3E%3DIF(I12%3D%22trout%22%2CIF(M12%26gt%3B0%2CSUM((((M12%2F100)%5E3)*10)*0.105)%20-%20Works%3CBR%20%2F%3E%3CBR%20%2F%3ENow%20Im%20having%20trouble%20combining%20them%3B%20(last%20attempt)%3CBR%20%2F%3E%3CBR%20%2F%3EIF(I11%3D%22trout%22%2CIF(M11%26gt%3B0%2CSUM((((M11%2F100%5E3)*10)*1.049))%2CIF(I11%3D%22salmon%22%2CIF(M11%26gt%3B0%2CSUM((((M11%2F100)%5E3)*10)*0.9711)%2CSUM(0.004*N11%5E2.535)))))%3CBR%20%2F%3E%3CBR%20%2F%3EAnyone%20got%20tips%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1598187%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1598224%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20multiple%20if(functions%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1598224%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F764155%22%20target%3D%22_blank%22%3E%40fishdude%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20logic%20In%20short%3A%26nbsp%3B%3CBR%20%2F%3EIF%20A(salmon)%20and%20B(1)%2C%20and%20either%20C(length)%20or%20D(height)%3B%20use%20E%20formula*C%20if%20C%20present%20or%20F%20formula*%20D%20if%20D%20present.%20But%20if%20A(trout)%20and%20B(1)%2C%20and%20C%20(length)%20use%20G%20formula*C%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1600835%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20multiple%20if(functions%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1600835%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F764155%22%20target%3D%22_blank%22%3E%40fishdude%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(I12%3D%22trout%22%2C%0A%20%20%20IF(M12%26gt%3B0%2C%0A%20%20%20%20%20((M12%2F100)%5E3)*10*0.105%2C%0A%20%20%20%20%200%0A%20%20%20)%2C%0A%20IF(I12%3D%22salmon%22%2C%0A%20%20%20IF(M12%26gt%3B0%2C%0A%20%20%20%20%20((M12%2F100)%5E3)*10*0.9711%2C%0A%20%20%20%20%20%200.004*N12%5E2.535%0A%20%20%20)%2C%0A%200))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi! 

So this one is a bit tricky. I'm trying to connect these three separate formulas into one cell: 
I got one formula which is - if this cell is salmon , and there is 1+ of them, choose this cell with input length cm to calculate weight, if not pick the cell with height (n12) and use this formula.

=IF(I12="salmon",IF(M12>0,SUM((((M12/100)^3)*10)*0.9711),SUM(0.004*N12^2.535))) - Works

then I got the same one for trout but without the height formula
=IF(I12="trout",IF(M12>0,SUM((((M12/100)^3)*10)*0.105) - Works

Now Im having trouble combining them; (last attempt)

IF(I11="trout",IF(M11>0,SUM((((M11/100^3)*10)*1.049)),IF(I11="salmon",IF(M11>0,SUM((((M11/100)^3)*10)*0.9711),SUM(0.004*N11^2.535)))))

Anyone got tips?

2 Replies
Highlighted

@fishdude 

The logic In short: 
IF A(salmon) and B(1), and either C(length) or D(height); use E formula*C if C present or F formula* D if D present. But if A(trout) and B(1), and C (length) use G formula*C



@fishdude 

As variant

=IF(I12="trout",
   IF(M12>0,
     ((M12/100)^3)*10*0.105,
     0
   ),
 IF(I12="salmon",
   IF(M12>0,
     ((M12/100)^3)*10*0.9711,
      0.004*N12^2.535
   ),
 0))