IF and AND formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-1407190%22%20slang%3D%22en-US%22%3EIF%20and%20AND%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1407190%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20having%20trouble%20trying%20to%20formulate%20this.%26nbsp%3B%20My%20current%20formula%20is%26nbsp%3B%3DIF(AND(%24C%247%26gt%3B%3D1.2%2C%24C%247%26lt%3B%3D1.2)%2CSUM(F6-%24D%247))%20which%20works%20perfectly.%26nbsp%3B%20I%20basically%20want%20to%20make%20it%20so%20it%20checks%20to%20see%20if%20C%20is%20%26gt%3B%3D%20or%20%26lt%3B%3D%20other%20numbers%20as%20well.%26nbsp%3B%20For%20example%2C%20I%20thought%20%3DIF(AND(%24C%247%26gt%3B%3D%3CSTRONG%3E1.2%3C%2FSTRONG%3E%2C%24C%247%26lt%3B%3D%3CSTRONG%3E1.2%3C%2FSTRONG%3E)%2CSUM(F6-%24D%247))%2C%20IF(AND(%24C%247%26gt%3B%3D%3CSTRONG%3E1.3%3C%2FSTRONG%3E%2C%24C%247%26lt%3B%3D%3CSTRONG%3E1.3%3C%2FSTRONG%3E)%2CSUM(F6-%24D%247))%20would%20work%20since%20I'm%20adding%20another%20IF%20formula%20but%20it%20does%20not.%26nbsp%3B%20I%20want%20it%20to%20check%20to%20see%20if%20it%20equals%20a%20certain%20value%2C%20and%20if%20so%20i%20want%20it%20to%20subtract%20a%20specific%20cell%20from%20another%20cell.%26nbsp%3B%20If%20you%20need%20a%20copy%20of%20my%20excel%20spreadsheet%20to%20see%20what%20I'm%20saying%2C%20i%20will%20be%20glad%20to%20provide%20that.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1407190%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-1407332%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20and%20AND%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1407332%22%20slang%3D%22en-US%22%3ETwo%20suggestions%20you%20can%20play%20with.%20The%20first%20one%20will%20return%200%20if%20C7%20is%20not%20equal%20to%201.2%20or%201.3.%20The%20second%2C%20IF%20function%2C%20will%20return%20FALSE%20if%20C7%20is%20not%20equal%20to%201.2%20or%201.3%2C%20but%20you%20can%20add%20on%20to%20it%20to%20do%20whatever%20else%20you%20may%20need%20if%20the%20condition%20is%20false.%3CBR%20%2F%3E%3CBR%20%2F%3E%3DOR(%24C%247%3D%7B1.2%2C1.3%7D)*(F6-%24D%247)%3CBR%20%2F%3E%3CBR%20%2F%3E%3DIF(OR(%24C%247%3D%7B1.2%2C1.3%7D)%2C(F6-%24D%247))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1409800%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20and%20AND%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1409800%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%26nbsp%3B%20I%20attached%20the%20screenshot%20of%20my%20excel%20spreadsheet.%26nbsp%3B%20I'm%20basically%20trying%20to%20make%20an%20automated%20inventory%20tracker.%26nbsp%3B%20I've%20tried%20to%20come%20up%20with%20formulas%20for%20quite%20some%20time%20now%20and%20couldn't%20figure%20it%20out.%26nbsp%3B%20I%20want%20to%20input%20a%20specific%20value%20into%20the%20%3CSTRONG%3Etype%3C%2FSTRONG%3E%20column%20and%20have%20the%20inventory%20cells%20change%20based%20on%20the%20specific%20value.%26nbsp%3B%20For%20example%2C%20if%20i%20put%201.2%20i%20want%20it%20to%20take%208%2C000%20from%20the%20columns%20%22%3CSTRONG%3E2%2C%20Forms%2C%20Stamp%201%2C%20Envelope%2C%20With%20Checkmarks%3C%2FSTRONG%3E.%22%20I%20don't%20know%20if%20this%20is%20possible%2C%20but%20it%20would%20be%20cool%20to%20have.%26nbsp%3B%20Cell%20F7%2C%20works%2C%20but%20i%20also%20want%20it%20to%20be%20able%20to%20check%20other%20values%20in%20the%26nbsp%3B%3CSTRONG%3Etype%26nbsp%3B%3C%2FSTRONG%3Ecolumn%20such%20as%201.1%2C%201.3%2C%202%2C%203%2C%20etc.%26nbsp%3B%20I%20don't%20know%20if%20this%20makes%20sense%2C%20but%20please%20let%20me%20know%20if%20you%20can%20think%20of%20something.%26nbsp%3B%20I%20appreciate%20it!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1410152%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20and%20AND%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1410152%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675087%22%20target%3D%22_blank%22%3E%40davies915%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBe%20sure%20to%20make%20a%20backup%20before%20trying.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20add%20some%20rows%20above%20my%20column%20headers%20and%20put%20in%20all%20of%20the%20types%20that%20are%20applicable%20to%20each%20inventory%20item%20(grouped%20so%20you%20can%20hide%20them%20easily).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20use%20a%20formula%20to%20check%20the%20type%20in%20Column%20C%20against%20the%20type%20applicable%20to%20each%20field%20and%20deduct%20the%20amount%20in%20Column%20D%20from%20the%20previous%20row.%20I%20will%20try%20to%20upload%20a%20screenshot.%20The%20formula%20in%20cell%20E8%20must%20be%20confirmed%20with%20Ctrl%2BShift%2BEnter%20(not%20just%20enter)%20and%20then%20copied%20down%2Facross.%20I%20like%20to%20leave%20a%20blank%20row%20in%20Row%205%20so%20that%20I%20could%20add%20items%20later%20(insert%20additional%20rows%20at%20Row%205%20and%20the%20table%20formulas%20will%20update%20automatically%20since%20they%20reference%20Row%205).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1410157%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20and%20AND%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1410157%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675087%22%20target%3D%22_blank%22%3E%40davies915%3C%2FA%3E%26nbsp%3B%20Also%2C%20when%20you%20enter%20the%20formula%20in%20E8%2C%20don't%20type%20the%20braces%20%7B%20%7D.%20Excel%20will%20add%20those%20when%20you%20hit%20Ctrl%2BShift%2BEnter.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1410183%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20and%20AND%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1410183%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675087%22%20target%3D%22_blank%22%3E%40davies915%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%2C%20if%20you%20have%20a%20lot%20of%20different%20types%20and%20think%20it%20would%20be%20easier%20to%20manage%20what%20items%20go%20with%20each%20type%20by%20using%20a%20table%20to%20map%20the%20type%20to%20the%20products%2C%20then%20we%20could%20set%20up%20a%20table%20on%20a%20separate%20sheet%20and%20modify%20the%20formula%20to%20look%20up%20the%20type%20in%20the%20table%20to%20identify%20which%20items%20it%20applies%20to.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello,

 

I am having trouble trying to formulate this.  My current formula is =IF(AND($C$7>=1.2,$C$7<=1.2),SUM(F6-$D$7)) which works perfectly.  I basically want to make it so it checks to see if C is >= or <= other numbers as well.  For example, I thought =IF(AND($C$7>=1.2,$C$7<=1.2),SUM(F6-$D$7)), IF(AND($C$7>=1.3,$C$7<=1.3),SUM(F6-$D$7)) would work since I'm adding another IF formula but it does not.  I want it to check to see if it equals a certain value, and if so i want it to subtract a specific cell from another cell.  If you need a copy of my excel spreadsheet to see what I'm saying, i will be glad to provide that.  

5 Replies
Highlighted
Two suggestions you can play with. The first one will return 0 if C7 is not equal to 1.2 or 1.3. The second, IF function, will return FALSE if C7 is not equal to 1.2 or 1.3, but you can add on to it to do whatever else you may need if the condition is false.

=OR($C$7={1.2,1.3})*(F6-$D$7)

=IF(OR($C$7={1.2,1.3}),(F6-$D$7))
Highlighted

@JMB17  I attached the screenshot of my excel spreadsheet.  I'm basically trying to make an automated inventory tracker.  I've tried to come up with formulas for quite some time now and couldn't figure it out.  I want to input a specific value into the type column and have the inventory cells change based on the specific value.  For example, if i put 1.2 i want it to take 8,000 from the columns "2, Forms, Stamp 1, Envelope, With Checkmarks." I don't know if this is possible, but it would be cool to have.  Cell F7, works, but i also want it to be able to check other values in the type column such as 1.1, 1.3, 2, 3, etc.  I don't know if this makes sense, but please let me know if you can think of something.  I appreciate it!

Highlighted

@davies915 

 

Be sure to make a backup before trying.

 

I would add some rows above my column headers and put in all of the types that are applicable to each inventory item (grouped so you can hide them easily).

 

Then use a formula to check the type in Column C against the type applicable to each field and deduct the amount in Column D from the previous row. I will try to upload a screenshot. The formula in cell E8 must be confirmed with Ctrl+Shift+Enter (not just enter) and then copied down/across. I like to leave a blank row in Row 5 so that I could add items later (insert additional rows at Row 5 and the table formulas will update automatically since they reference Row 5).

 

 

 

 

Highlighted

@davies915  Also, when you enter the formula in E8, don't type the braces { }. Excel will add those when you hit Ctrl+Shift+Enter.

Highlighted

@davies915 

 

Or, if you have a lot of different types and think it would be easier to manage what items go with each type by using a table to map the type to the products, then we could set up a table on a separate sheet and modify the formula to look up the type in the table to identify which items it applies to.