Aug 02 2019 07:14 PM - edited Aug 02 2019 07:21 PM
Hi!
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.
If column 1 is Brass, multiply column 2 (grams) with column 5 (price per grams), otherwise leave cell blank
If column 1 is Silver, multiply column 2 (grams) with column 6 (price per grams), otherwise leave cell blank
If column 1 is Pale Gold, multiply column 2 (grams) with column 7 (price per grams), otherwise leave cell blank
If column 1 is Rose Gold, multiply column 2 (grams) with column 8 (price per grams), otherwise leave cell blank
If column 1 is White Gold, multiply column 2 (grams) with column 9 (price per grams), otherwise leave cell blank
I tried this
=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],"")
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. "???"
Can anyone help me with this?
THANK YOU!
Aug 02 2019 07:48 PM
SolutionHi @gminii ,
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])
This should also solve your issue with formatting
Thanks
Yury
Aug 02 2019 09:59 PM
Thank you for your quick respone @Yury Tokarev
I tried your solution but it results in #VALUE!
Any idea what could be the cause of this?
Aug 02 2019 10:05 PM
Aug 02 2019 10:14 PM
Got it! Thanks for your help @Yury Tokarev You are gold!
Aug 02 2019 07:48 PM
SolutionHi @gminii ,
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])
This should also solve your issue with formatting
Thanks
Yury