Discussion Multiple nested IF formula in a table with calculations in Excel
https://techcommunity.microsoft.com/t5/excel/multiple-nested-if-formula-in-a-table-with-calculations/m-p/786551#M37670
<P>Hi!</P><P>I am a newbie in excel and am trying to create a multiple nested IF formula to calculate a price based on weight for different metals for jewelry.</P><P> </P><P>If column 1 is Brass, multiply column 2 (grams) with column 5 (price per grams), otherwise leave cell blank</P><P>If column 1 is Silver, multiply column 2 (grams) with column 6 (price per grams), otherwise leave cell blank</P><P>If column 1 is Pale Gold, multiply column 2 (grams) with column 7 (price per grams), otherwise leave cell blank</P><P>If column 1 is Rose Gold, multiply column 2 (grams) with column 8 (price per grams), otherwise leave cell blank</P><P>If column 1 is White Gold, multiply column 2 (grams) with column 9 (price per grams), otherwise leave cell blank</P><P> </P><P>I tried this</P><P>=IF([@Column1]="BR",[@Column2]*[@Column5],"")&IF([@Column1]="SS",[@Column2]*[@Column6],"")&IF([@Column1]="PG",[@Column2]*[@Column7],"")&IF([@Column1]="RG",[@Column2]*[@Column8],"")&IF([@Column1]="WG",[@Column2]*[@Column9],"")</P><P> </P><P>However, the result is the correct number but it gives me 11 decimal places, even though the cell is formatted to 2 decimal places and it won't let me format the cell to a currency. "???"</P><P> </P><P>Can anyone help me with this? </P><P>THANK YOU!</P>Sat, 03 Aug 2019 02:21:06 GMTgminii2019-08-03T02:21:06ZMultiple nested IF formula in a table with calculations
https://techcommunity.microsoft.com/t5/excel/multiple-nested-if-formula-in-a-table-with-calculations/m-p/786551#M37670
<P>Hi!</P><P>I am a newbie in excel and am trying to create a multiple nested IF formula to calculate a price based on weight for different metals for jewelry.</P><P> </P><P>If column 1 is Brass, multiply column 2 (grams) with column 5 (price per grams), otherwise leave cell blank</P><P>If column 1 is Silver, multiply column 2 (grams) with column 6 (price per grams), otherwise leave cell blank</P><P>If column 1 is Pale Gold, multiply column 2 (grams) with column 7 (price per grams), otherwise leave cell blank</P><P>If column 1 is Rose Gold, multiply column 2 (grams) with column 8 (price per grams), otherwise leave cell blank</P><P>If column 1 is White Gold, multiply column 2 (grams) with column 9 (price per grams), otherwise leave cell blank</P><P> </P><P>I tried this</P><P>=IF([@Column1]="BR",[@Column2]*[@Column5],"")&IF([@Column1]="SS",[@Column2]*[@Column6],"")&IF([@Column1]="PG",[@Column2]*[@Column7],"")&IF([@Column1]="RG",[@Column2]*[@Column8],"")&IF([@Column1]="WG",[@Column2]*[@Column9],"")</P><P> </P><P>However, the result is the correct number but it gives me 11 decimal places, even though the cell is formatted to 2 decimal places and it won't let me format the cell to a currency. "???"</P><P> </P><P>Can anyone help me with this? </P><P>THANK YOU!</P>Sat, 03 Aug 2019 02:21:06 GMThttps://techcommunity.microsoft.com/t5/excel/multiple-nested-if-formula-in-a-table-with-calculations/m-p/786551#M37670gminii2019-08-03T02:21:06ZRe: Multiple nested IF formula in a table with calculations
https://techcommunity.microsoft.com/t5/excel/multiple-nested-if-formula-in-a-table-with-calculations/m-p/786573#M37672
<P>Hi <LI-USER uid="386655"></LI-USER> ,</P><P> </P><P>I would not recommend using nested IFs, as they make formula complex. Instead, I suggest using SUMPRODUCT. If the names of the columns 5 to 9 were 'BR', 'SS', 'PG', 'RG' and 'WG' respectively, and, assuming that the name of the table is 'MyTable', your formula would be =SUMPRODUCT((MyTable[[#Headers],[BR]:[WG]]=[@Column1])*MyTable[@[BR]:[WG]]*[@Column2])</P><P> </P><P>This should also solve your issue with formatting</P><P> </P><P>Thanks</P><P>Yury</P>Sat, 03 Aug 2019 02:48:13 GMThttps://techcommunity.microsoft.com/t5/excel/multiple-nested-if-formula-in-a-table-with-calculations/m-p/786573#M37672Yury Tokarev2019-08-03T02:48:13ZRe: Multiple nested IF formula in a table with calculations
https://techcommunity.microsoft.com/t5/excel/multiple-nested-if-formula-in-a-table-with-calculations/m-p/786620#M37675
<P>Thank you for your quick respone <LI-USER uid="15833"></LI-USER> </P><P>I tried your solution but it results in #VALUE!</P><P>Any idea what could be the cause of this?</P>Sat, 03 Aug 2019 04:59:15 GMThttps://techcommunity.microsoft.com/t5/excel/multiple-nested-if-formula-in-a-table-with-calculations/m-p/786620#M37675gminii2019-08-03T04:59:15ZRe: Multiple nested IF formula in a table with calculations
https://techcommunity.microsoft.com/t5/excel/multiple-nested-if-formula-in-a-table-with-calculations/m-p/786621#M37676
<P>Hi <LI-USER uid="386655"></LI-USER>,</P><P> </P><P>please find attached the file with the solution. </P><P> </P><P>Hope it helps</P><P>Yury</P>Sat, 03 Aug 2019 05:05:08 GMThttps://techcommunity.microsoft.com/t5/excel/multiple-nested-if-formula-in-a-table-with-calculations/m-p/786621#M37676Yury Tokarev2019-08-03T05:05:08ZRe: Multiple nested IF formula in a table with calculations
https://techcommunity.microsoft.com/t5/excel/multiple-nested-if-formula-in-a-table-with-calculations/m-p/786622#M37677
<P>Got it! Thanks for your help <LI-USER uid="15833"></LI-USER> You are gold!</P>Sat, 03 Aug 2019 05:14:29 GMThttps://techcommunity.microsoft.com/t5/excel/multiple-nested-if-formula-in-a-table-with-calculations/m-p/786622#M37677gminii2019-08-03T05:14:29Z