Forum Discussion
Solved
Trying to Formulate IF with multiple variables
I'm trying to find a quick and easy way to work with inventory quantities in shopify - and need a formula that will allow me to do the efficiently in excel.
Basically, for every 3 items in our warehouse, we add 1 product to our online inventory with Harvey Nichols (UK department store) -- although these physically sit in our Warehouse.
I need a formula allowing me to +1 for every 3 and then minus that 1 from the Warehouse column.
Basically something like this - but no idea how to actually formulate - can anyone help?
IF O2 >3 +1 to M2 and -1 from O2
- To truly do this you would need a Macro.
I would suggest that you instead do this with two formulas in two additional columns. (we'll use A and B for this example)
Column A (Calculates new values for Colum M)
Cell A1=IF(INDIRECT("O1:O"&(MAX(IF(ISBLANK(O:O),0,ROW(O:O)))))>3,INDIRECT("M1:M"&(MAX(IF(ISBLANK(M:M),0,ROW(M:M)))))+1,INDIRECT("M1:M"&(MAX(IF(ISBLANK(M:M),0,ROW(M:M))))))
Column B (Calculates New values for Column O)
B1=IF(INDIRECT("O1:O"&(MAX(IF(ISBLANK(O:O),0,ROW(O:O)))))>3,INDIRECT("O1:O"&(MAX(IF(ISBLANK(O:O),0,ROW(O:O)))))-1,INDIRECT("O1:O"&(MAX(IF(ISBLANK(O:O),0,ROW(O:O))))))
You will need to go to the "Formulas" tab on the ribbon and click on "Calculation Options" then set that to "Manual" for this method to work.
then you select calculate now from the formulas ribbon
Select column A copy it and paste as values into column N
Select Column B copy it and paste as values into Column O
3 Replies
- To truly do this you would need a Macro.
I would suggest that you instead do this with two formulas in two additional columns. (we'll use A and B for this example)
Column A (Calculates new values for Colum M)
Cell A1=IF(INDIRECT("O1:O"&(MAX(IF(ISBLANK(O:O),0,ROW(O:O)))))>3,INDIRECT("M1:M"&(MAX(IF(ISBLANK(M:M),0,ROW(M:M)))))+1,INDIRECT("M1:M"&(MAX(IF(ISBLANK(M:M),0,ROW(M:M))))))
Column B (Calculates New values for Column O)
B1=IF(INDIRECT("O1:O"&(MAX(IF(ISBLANK(O:O),0,ROW(O:O)))))>3,INDIRECT("O1:O"&(MAX(IF(ISBLANK(O:O),0,ROW(O:O)))))-1,INDIRECT("O1:O"&(MAX(IF(ISBLANK(O:O),0,ROW(O:O))))))
You will need to go to the "Formulas" tab on the ribbon and click on "Calculation Options" then set that to "Manual" for this method to work.
then you select calculate now from the formulas ribbon
Select column A copy it and paste as values into column N
Select Column B copy it and paste as values into Column O- Thank you SO much for this!! Just tried it and it works perfectly!
This is my first time ever using an online forum - what a great place the internet can be!!- I'm Glad it helped!
I am by no means the best person on this site. You will find a huge well of knowledge and great people on here who are happy to help.
