May 20 2020 05:55 PM
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.
May 20 2020 07:56 PM
May 21 2020 12:12 PM
@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!
May 21 2020 02:00 PM
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).
May 21 2020 02:02 PM
@davies915 Also, when you enter the formula in E8, don't type the braces { }. Excel will add those when you hit Ctrl+Shift+Enter.
May 21 2020 02:09 PM
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.