SOLVED
Home

Multiple nested IF formula in a table with calculations

%3CLINGO-SUB%20id%3D%22lingo-sub-786551%22%20slang%3D%22en-US%22%3EMultiple%20nested%20IF%20formula%20in%20a%20table%20with%20calculations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786551%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3C%2FP%3E%3CP%3EI%20am%20a%20newbie%20in%20excel%20and%20am%20trying%20to%20create%20a%20multiple%20nested%20IF%20formula%20to%20calculate%20a%20price%20based%20on%20weight%20for%20different%20metals%20for%20jewelry.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20column%201%20is%20Brass%2C%20multiply%20column%202%20(grams)%20with%20column%205%20(price%20per%20grams)%2C%20otherwise%20leave%20cell%20blank%3C%2FP%3E%3CP%3EIf%20column%201%20is%20Silver%2C%20multiply%20column%202%20(grams)%20with%20column%206%20(price%20per%20grams)%2C%20otherwise%20leave%20cell%20blank%3C%2FP%3E%3CP%3EIf%20column%201%20is%20Pale%20Gold%2C%20multiply%20column%202%20(grams)%20with%20column%207%20(price%20per%20grams)%2C%20otherwise%20leave%20cell%20blank%3C%2FP%3E%3CP%3EIf%20column%201%20is%20Rose%20Gold%2C%20multiply%20column%202%20(grams)%20with%20column%208%20(price%20per%20grams)%2C%20otherwise%20leave%20cell%20blank%3C%2FP%3E%3CP%3EIf%20column%201%20is%20White%20Gold%2C%20multiply%20column%202%20(grams)%20with%20column%209%20(price%20per%20grams)%2C%20otherwise%20leave%20cell%20blank%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20this%3C%2FP%3E%3CP%3E%3DIF(%5B%40Column1%5D%3D%22BR%22%2C%5B%40Column2%5D*%5B%40Column5%5D%2C%22%22)%26amp%3BIF(%5B%40Column1%5D%3D%22SS%22%2C%5B%40Column2%5D*%5B%40Column6%5D%2C%22%22)%26amp%3BIF(%5B%40Column1%5D%3D%22PG%22%2C%5B%40Column2%5D*%5B%40Column7%5D%2C%22%22)%26amp%3BIF(%5B%40Column1%5D%3D%22RG%22%2C%5B%40Column2%5D*%5B%40Column8%5D%2C%22%22)%26amp%3BIF(%5B%40Column1%5D%3D%22WG%22%2C%5B%40Column2%5D*%5B%40Column9%5D%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20the%20result%20is%20the%20correct%20number%20but%20it%20gives%20me%2011%20decimal%20places%2C%20even%20though%20the%20cell%20is%20formatted%20to%202%20decimal%20places%20and%20it%20won't%20let%20me%20format%20the%20cell%20to%20a%20currency.%20%22%3F%3F%3F%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%20me%20with%20this%3F%26nbsp%3B%3C%2FP%3E%3CP%3ETHANK%20YOU!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-786551%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-786573%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20nested%20IF%20formula%20in%20a%20table%20with%20calculations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786573%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F386655%22%20target%3D%22_blank%22%3E%40gminii%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20not%20recommend%20using%20nested%20IFs%2C%20as%20they%20make%20formula%20complex.%20Instead%2C%20I%20suggest%20using%20SUMPRODUCT.%20If%20the%20names%20of%20the%20columns%205%20to%209%20were%20'BR'%2C%20'SS'%2C%20'PG'%2C%20'RG'%20and%20'WG'%20respectively%2C%20and%2C%20assuming%20that%20the%20name%20of%20the%20table%20is%20'MyTable'%2C%20your%20formula%20would%20be%20%3DSUMPRODUCT((MyTable%5B%5B%23Headers%5D%2C%5BBR%5D%3A%5BWG%5D%5D%3D%5B%40Column1%5D)*MyTable%5B%40%5BBR%5D%3A%5BWG%5D%5D*%5B%40Column2%5D)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20should%20also%20solve%20your%20issue%20with%20formatting%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EYury%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-786620%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20nested%20IF%20formula%20in%20a%20table%20with%20calculations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786620%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20your%20quick%20respone%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F15833%22%20target%3D%22_blank%22%3E%40Yury%20Tokarev%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20your%20solution%20but%20it%20results%20in%20%23VALUE!%3C%2FP%3E%3CP%3EAny%20idea%20what%20could%20be%20the%20cause%20of%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-786622%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20nested%20IF%20formula%20in%20a%20table%20with%20calculations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786622%22%20slang%3D%22en-US%22%3E%3CP%3EGot%20it!%20Thanks%20for%20your%20help%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F15833%22%20target%3D%22_blank%22%3E%40Yury%20Tokarev%3C%2FA%3E%26nbsp%3BYou%20are%20gold!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-786621%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20nested%20IF%20formula%20in%20a%20table%20with%20calculations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786621%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F386655%22%20target%3D%22_blank%22%3E%40gminii%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eplease%20find%20attached%20the%20file%20with%20the%20solution.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20it%20helps%3C%2FP%3E%3CP%3EYury%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
gminii
Occasional Contributor

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!

4 Replies
Solution

Hi @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

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?

Hi @gminii,

 

please find attached the file with the solution. 

 

Hope it helps

Yury

Got it! Thanks for your help @Yury Tokarev You are gold!

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies