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 ou...
- 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
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
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!!
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.