I was able to elevate the formula to where I need it, but I am not able to understand why it will return a value of "0" when the MOQ/MPQ is over 100 pcs? I believe the Round is what is causing this? There are also a few other scenarios I am trying to include into the formula. Any suggestions?
Code:
=(IF(G2="S - Stock",IF(H2="TR",ROUND(100/A2/E2,0)*E2,ROUND(100/A2,0)),IF(H2="TR",IF(B2>=ROUND(100/A2/E2,0)*E2,ROUND(100/A2/E2,0)*E2,IF(B2>=ROUND(100/A2/E2,0)*E2,IF(D2="",C2,D2),ROUND(100/A2/E2,0)*E2)),IF(B2>=ROUND(100/A2/E2,0)*E2,ROUND(100/A2,0),IF(B2>=ROUND(100/A2/E2,0)*E2,IF(D2="",C2,D2),ROUND(100/A2/E2,0)*E2)))))
Price |
Stock |
MOQ |
Approved MOQ |
MPQ |
Approved MPQ |
Stock Profile |
Package |
Formula |
Comments |
1.1 |
|
1000 |
|
1000 |
|
N - Non |
TR |
0 |
why is MOQ "0"? If I lower the MOQ/MPQ to 100 then it will work??? |
|
1.0 |
|
1500 |
|
500 |
|
N - Non |
TR |
0 |
why is MOQ "0" If I lower the MOQ/MPQ to 100 then it will work??? |
|
1.4824 |
3000 |
3000 |
|
3000 |
|
S - Stock |
TR |
0 |
why is MOQ "0" If I lower the MOQ/MPQ to 100 then it will work??? |
|
.0429 |
|
3000 |
|
3000 |
|
S - Stock |
TR |
0 |
why is MOQ "0" If I lower the MOQ/MPQ to 100 then it will work??? |
|
53.49 |
|
1 |
|
1 |
|
N - Non |
BLK |
2 |
|
23.54 |
|
2646 |
|
126 |
122 |
N - Non |
BLK |
4 |
How to make Approved MPQ supersede? |
|
17.21 |
47 |
1 |
|
1 |
|
S - Stock |
BLK |
6 |
correct |
.1587 |
6367 |
7200 |
|
100 |
|
S - Stock |
BLK |
630 |
correct |
.04 |
|
|
|
|
|
|
|
#VALUE! |
How to make Approved MOQ/MPQ supercede? And one step further to still work without Stock Profile |
|
.052 |
|
|
|
|
|
|
|
#VALUE! |
How to make Approved MOQ/MPQ supercede? And one step further to still work without Stock Profile |
|
Bookmarks