Looking for some help!

%3CLINGO-SUB%20id%3D%22lingo-sub-1390165%22%20slang%3D%22en-US%22%3ELooking%20for%20some%20help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1390165%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20a%20formula!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20make%20a%20spreadsheet%20for%20bar%20inventory%20purposes.%20I%20have%20one%20cell%20that%20is%20a%20value%20of%20%241.36%20per%20oz%20of%20liquid%20and%20another%20cell%20that%20has%20a%20pre%20existing%20formula%20that%20equals%20two%20other%20cells.%20Those%20two%20cells%20are%20hard%20numbers.%20For%20example%2C%204%20bottles%20in%20one%20cell%20minus%203.9%20bottles%20in%20another%20which%20equals%20.1%20of%20a%20bottle%20in%20yet%20another%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20attached%20a%20pic%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20need%20is%20for%20every%20.01%20variation%20in%20I3%20to%20equal%20the%20%241.36%20value%20per%20ounce%20in%20D3%20in%20J3.%20But%20I%20need%20everytime%20there%20is%20more%20variation%20to%20continue%20to%20calculate.%20So%20if%20the%20variation%20was%20.02%20it%20would%20calculate%242.72.%20There%20is%20a%20pre%20existing%20formula%20in%20both%20columns.%20I%20then%20need%20to%20copy%20that%20formula%20all%20the%20way%20down%20the%20sheet%2C%20which%20I%20know%20how%20to%20do.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Excel.jpg%22%20style%3D%22width%3A%20749px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F191719iC21D91107C3562B4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Excel.jpg%22%20alt%3D%22Excel.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1390165%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1390189%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20some%20help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1390189%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%2F668331%22%20target%3D%22_blank%22%3E%40morgansmith%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20need%20the%20formula%20in%20J3%20to%20multiply%20the%20contents%20of%20both%20D3%20%26amp%3B%20I3%3F%20if%20so%20just%20use%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D%20D3%20*%20I3.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20however%2C%20you%20wanted%20additional%20conditions%2C%20please%20communicate%20or%20share%20the%20file%20that%20you%20can%20use%20as%20a%20sample.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1390212%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20some%20help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1390212%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F622486%22%20target%3D%22_blank%22%3E%40wumolad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20not%20looking%20for%20it%20to%20just%20multiply.%20I%20need%20J3%20to%20equal%20I3%20but%20in%20dollar%20amount.%20The%20issue%20I'm%20having%20is%20that%20I3%20is%20going%20to%20be%20a%20different%20value%20for%20each%20item.%20Essentially%20G3-H3%20%3D%20I3%2C%20which%20should%20also%20equal%20J3.%20I3%20needs%20to%20be%20the%20percentage%20it%20is%20but%20I%20need%20a%20dollar%20amount%20for%20J3%20and%20I%20need%20it%20to%20be%20able%20to%20change%20in%20dollar%20format%20as%20I3%20changes%20in%20decimal%20format.%20The%20more%20variance%20in%20I3%20the%20more%20the%20variance%20should%20change%20in%20J3.%20%3CSTRONG%3E%3CU%3EEach%20.01%20variance%20in%20I3%20needs%20to%20equal%20%241.36%20variance%20in%20J3.%26nbsp%3B%3C%2FU%3E%3C%2FSTRONG%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20that%20helps%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1390237%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20some%20help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1390237%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%2F668331%22%20target%3D%22_blank%22%3E%40morgansmith%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAre%20you%20able%20to%20send%20a%20sample%20worksheet%20and%20put%20the%20value%20you%20intend%20to%20achieve%20for%20each%20case%20as%20well%20as%20the%20desired%20formatting%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20couldn't%20get%20your%20point%20clearly.%20A%20sample%20will%20aid%20in%20understanding.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1390407%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20some%20help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1390407%22%20slang%3D%22en-US%22%3EI%20think%20what%20you%20might%20need%20in%20J3%20is%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3D-100*I3*D3%3CBR%20%2F%3E%3CBR%20%2F%3Eso%20if%20I3%20contains%20-0.01%20you%20would%20get%20-100*-0.01%20%3D%201%20*%201.36%20%3D%201.36%3CBR%20%2F%3Eif%20I3%20contains%20-0.02%20you%20would%20get%20-100*-0.02%20%3D%202%20*%201.36%20%3D%202.72%3CBR%20%2F%3Eand%20so%20on.%3CBR%20%2F%3E%3CBR%20%2F%3EDoes%20that%20help%3F%3CBR%20%2F%3E%3CBR%20%2F%3EPeter%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1396817%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20some%20help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1396817%22%20slang%3D%22en-US%22%3EThat%20worked!%20Thanks!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1397220%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20some%20help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1397220%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F668331%22%20target%3D%22_blank%22%3E%40morgansmith%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat's%20great%20-%20can%20I%20ask%20you%20to%20please%20mark%20the%20solution%20as%20complete.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%3C%2FP%3E%3CP%3EPeter%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I need a formula!

 

I'm trying to make a spreadsheet for bar inventory purposes. I have one cell that is a value of $1.36 per oz of liquid and another cell that has a pre existing formula that equals two other cells. Those two cells are hard numbers. For example, 4 bottles in one cell minus 3.9 bottles in another which equals .1 of a bottle in yet another cell.

 

I've attached a pic below.

 

What I need is for every .01 variation in I3 to equal the $1.36 value per ounce in D3 in J3. But I need everytime there is more variation to continue to calculate. So if the variation was .02 it would calculate$2.72. There is a pre existing formula in both columns. I then need to copy that formula all the way down the sheet, which I know how to do.

 

Excel.jpg

 

 

6 Replies
Highlighted

Hi @morgansmith 

 

Do you need the formula in J3 to multiply the contents of both D3 & I3? if so just use

 

= D3 * I3.

 

If however, you wanted additional conditions, please communicate or share the file that you can use as a sample.

 

Cheers

Highlighted

@wumolad 

 

I'm not looking for it to just multiply. I need J3 to equal I3 but in dollar amount. The issue I'm having is that I3 is going to be a different value for each item. Essentially G3-H3 = I3, which should also equal J3. I3 needs to be the percentage it is but I need a dollar amount for J3 and I need it to be able to change in dollar format as I3 changes in decimal format. The more variance in I3 the more the variance should change in J3. Each .01 variance in I3 needs to equal $1.36 variance in J3.  

 

Maybe that helps?

Highlighted

Hi @morgansmith 

 

Are you able to send a sample worksheet and put the value you intend to achieve for each case as well as the desired formatting?

 

I couldn't get your point clearly. A sample will aid in understanding.

 

Cheers

Highlighted
I think what you might need in J3 is:

=-100*I3*D3

so if I3 contains -0.01 you would get -100*-0.01 = 1 * 1.36 = 1.36
if I3 contains -0.02 you would get -100*-0.02 = 2 * 1.36 = 2.72
and so on.

Does that help?

Peter
Highlighted
That worked! Thanks!
Highlighted

@morgansmith 

 

That's great - can I ask you to please mark the solution as complete.

 

thanks

Peter