SOLVED

Multiplier if a cell =P

%3CLINGO-SUB%20id%3D%22lingo-sub-2247286%22%20slang%3D%22en-US%22%3EMultiplier%20if%20a%20cell%20%3DP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2247286%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20I%20have%20a%20cost%20speadsheet%20that%20I%20need%20to%20calculate%20cost.%20There%20are%20two%20multipliers%20used%20from%20the%20list%20price%20column%20depending%20on%20the%20product.%20So%20I%20need%20to%20use%20.51%20if%20cell%20G11%20%3D%20P%20and%20.49%20if%20the%20cell%20has%20no%20value%20(blank)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2247286%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-2247319%22%20slang%3D%22en-US%22%3ERe%3A%20Multiplier%20if%20a%20cell%20%3DP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2247319%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1013927%22%20target%3D%22_blank%22%3E%40Hydro222%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20Read%20this%20Post%20with%20help.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fwelcome-to-your-excel-discussion-space%2Fm-p%2F2204395%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fwelcome-to-your-excel-discussion-space%2Fm-p%2F2204395%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EFrom%20your%20post%2C%20I%20can%20not%20see%20with%20what%20and%20who%20it%20should%20be%20multiplied%2C%20for%20example%200.51%20or%2051st%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EWith%20your%20permission%2C%20if%20I%20can%20recommend%20you%2C%20add%20a%20file%20(without%20sensitive%20data)%20to%20your%20project.%3C%2FP%3E%3CP%3EExplain%20your%20plans%20in%20relation%20to%20this%20file.%20So%20you%20can%20get%20a%20solution%20that%20is%20tailored%20to%20your%20needs%20much%20faster.%3C%2FP%3E%3CP%3EAt%20the%20same%20time%2C%20it%20is%20much%20easier%20for%20someone%20who%20wants%20to%20help%20to%20understand%20the%20subject.%3C%2FP%3E%3CP%3EKnowing%20the%20Excel%20version%20and%20operating%20system%20would%20also%20be%20an%20advantage.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2247593%22%20slang%3D%22en-US%22%3ERe%3A%20Multiplier%20if%20a%20cell%20%3DP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2247593%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1013927%22%20target%3D%22_blank%22%3E%40Hydro222%3C%2FA%3E%26nbsp%3BIn%20it's%20simplest%20form%20that%20could%20be%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(G11%3D%22P%22%2C0.51%2C0.49)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThis%20will%20test%20for%20G11%20%3D%20%22P%22.%20If%20so%2C%20take%200.51.%20If%20it%20is%20not%2C%20then%20take%20%3D0.49.%20So%2C%20if%20the%20value%20in%20G11%20is%20%3CSTRONG%3Enot%3C%2FSTRONG%3E%20equal%20to%20P%20(i.e.%20blank%20or%20anything%20else)%200.49%20will%20be%20returned.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

So I have a cost speadsheet that I need to calculate cost. There are two multipliers used from the list price column depending on the product. So I need to use .51 if cell G11 = P and .49 if the cell has no value (blank)

 

Thank you

10 Replies

@Hydro222 

Please Read this Post with help.

https://techcommunity.microsoft.com/t5/excel/welcome-to-your-excel-discussion-space/m-p/2204395

From your post, I can not see with what and who it should be multiplied, for example 0.51 or 51st

With your permission, if I can recommend you, add a file (without sensitive data) to your project.

Explain your plans in relation to this file. So you can get a solution that is tailored to your needs much faster.

At the same time, it is much easier for someone who wants to help to understand the subject.

Knowing the Excel version and operating system would also be an advantage.

 

Thank you for your understanding and patience

 

Nikolino

I know I don't know anything (Socrates)

@Hydro222 In it's simplest form that could be 

=IF(G11="P",0.51,0.49)

This will test for G11 = "P". If so, take 0.51. If it is not, then take =0.49. So, if the value in G11 is not equal to P (i.e. blank or anything else) 0.49 will be returned.

Thank you, I running Win10 with Office 2016.
Do not see how to add a file?
What I have is column (C) has list price. Column (J) would have cost based on using a multiplier of .49 So if C = 1.50 then J = .735 except when column (G) has a alpha value "P" then the multiplier changes to .51
I haven't been able to figure out how to write the formula.

@Hydro222 

 

Open full text editor

 

Drag and drop here or browse files to attach

Maximum size: 71 MB • Maximum attachments allowed: 5

 

Thank you for the reply. I need it to calculate the sum as well.
best response confirmed by Hydro222 (Occasional Contributor)
Solution

@Hydro222 

Please have a look at the inserted worksheet.

As I understand it, I have implemented the formula in columns J & K.

 

Thank you for you all understanding and patience

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

Thank you! That works perfect!

@Hydro222 In case you are interested in another solution. Consider this.

In J, calculate unit cost as:

=C3*(0.49+(G3="P")*0.02)

It takes the list price from C and multiplies it by either 0.49 or ( 0.49 plus an extra 0.02 ), in case G contains the letter P.

 

Calculate extended cost in I as:

=D3*K3

as in Quantity times Unit cost.

 

 

Great job on putting a condensed formula together. I like this . Thank you!