SOLVED

Can I use an "IF" function to return a "0" if true and to perform a calculation if not true

%3CLINGO-SUB%20id%3D%22lingo-sub-2424806%22%20slang%3D%22en-US%22%3ECan%20I%20use%20an%20%22IF%22%20function%20to%20return%20a%20%220%22%20if%20true%20and%20to%20perform%20a%20calculation%20if%20not%20true%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2424806%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20am%20attempting%20to%20write%20a%20formula%20which%20takes%20an%20inventory%20number%20which%20I%20input%20and%20compares%20it%20with%20what%20is%20expected%20to%20be%20on%20hand.%20If%20my%20count%20matches%20what%20is%20expected%20to%20be%20on%20hand%20then%20the%20gross%20sales%20would%20return%20a%20zero%20but%20if%20my%20hand%20count%20comes%20up%20less%20than%20what%20is%20expected%20to%20be%20on%20hand%20the%20formula%20would%20subtract%20what%20I%20counted%20from%20what%20is%20expected%20to%20be%20on%20hand%20and%20tell%20me%20the%20amount%20sold.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEx.%20on%20my%20spreadsheet%20%22gross%20inventory%22%20is%20equal%20to%20opening%20inventory%20plus%20additional%20inventory%20received.%20That%20value%20is%20stored%20in%20cell%20G6.%20In%20cell%20H6%20I%20input%20my%20physical%20count%20of%20inventory.%20So%2C%20if%20G6%20%3D%20500%20and%20my%20count%20(H6)%20%3D%20500%20then%20I%20want%20I6%20to%20%3D0.%20I%20can%20make%20that%20work%20fine%20on%20my%20sheet.%20But%20IF%20G6%20(Gross%20inventory)%20is%20greater%20than%20H6%20(hand%20counted%20inventory)%20I%20want%20I6%20to%20compute%20the%20difference.%20So%2C%20cell%20I6%20would%20either%20be%200%20or%20the%20difference%20between%20G6%20and%20H6.%20An%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2424806%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-2424860%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20I%20use%20an%20%22IF%22%20function%20to%20return%20a%20%220%22%20if%20true%20and%20to%20perform%20a%20calculat%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2424860%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1073349%22%20target%3D%22_blank%22%3E%40Xnex66%3C%2FA%3E%26nbsp%3BSomething%20like%20this%3F%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(H6%26gt%3BG6%2CH6-G6%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThis%20calculates%20H6-G6%20if%20H6%20is%20greater%20than%20G6.%20Anything%20else%20returns%200.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20a%20more%20funky%20formula%20could%20be%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D(H6-G6)*--(H6%26gt%3BG6)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2424864%22%20slang%3D%22en-US%22%3ERE%3A%20Can%20I%20use%20an%20%22IF%22%20function%20to%20return%20a%20%220%22%20if%20true%20and%20to%20perform%20a%20calculation%20if%20not%20true%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2424864%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much%20for%20taking%20time%20to%20help%20me%20out%20Riny%2C%20that%20worked%20like%20a%20charm%20and%20was%20much%20more%20simple%20and%20elegant%20than%20any%20monstrosities%20I%20was%20attempting%20to%20use.%3C%2FLINGO-BODY%3E
New Contributor

Hello, I am attempting to write a formula which takes an inventory number which I input and compares it with what is expected to be on hand. If my count matches what is expected to be on hand then the gross sales would return a zero but if my hand count comes up less than what is expected to be on hand the formula would subtract what I counted from what is expected to be on hand and tell me the amount sold.

 

Ex. on my spreadsheet "gross inventory" is equal to opening inventory plus additional inventory received. That value is stored in cell G6. In cell H6 I input my physical count of inventory. So, if G6 = 500 and my count (H6) = 500 then I want I6 to =0. I can make that work fine on my sheet. But IF G6 (Gross inventory) is greater than H6 (hand counted inventory) I want I6 to compute the difference. So, cell I6 would either be 0 or the difference between G6 and H6. An

3 Replies
best response confirmed by Xnex66 (New Contributor)
Solution

@Xnex66 Something like this?

=IF(H6>G6,H6-G6,0)

This calculates H6-G6 if H6 is greater than G6. Anything else returns 0.

 

Or a more funky formula could be:

=(H6-G6)*--(H6>G6)

 

Thank you so much for taking time to help me out Riny, that worked like a charm and was much more simple and elegant than any monstrosities I was attempting to use.