Spreadsheet: 746770d1631017025-formula-to-calculate-a-value-per-unique-part-with-multiple-warehouse-locations-excel-forum-question_-modified-090321-unallocated-inventory-3-
Sheet: REVISED 09-03-21

Item # | Item Desc. | Customer | Cat | Act | Location | Qty on Hand | UOM | Unit Cost | Value | Qty Needed | Value Needed | Balance | Revised Value | Qty Ordered | Lead Time | Order Value | [NAME] | Filter | QOO2 | Unallocated
C051 | [NAME] | ABC Company | PB | A | 1 | 23282 | EA | 0.10665 |  | 1999102.28 |  |  |  | 2032800 | 56 |  |  | NO |  |  | <<Desired Result
C051 | [NAME] | ABC Company | PB | A | 1MR | 0 | EA | 0.10665 |  | 0 |  |  |  | 0 | 56 |  |  | NO
C051 | [NAME] | ABC Company | PB | A | 1R | 0 | EA | 0.10665 |  | 0 |  |  |  | 0 | 56 |  |  | NO
C051 | [NAME] | ABC Company | PB | A | 2 | 254100 | EA | 0.10665 |  | 0 |  |  |  | 0 | 56 |  |  | YES
C051 | [NAME] | ABC Company | PB | A | 2MR | 0 | EA | 0.10665 |  | 0 |  |  |  | 0 | 56 |  |  | NO
C051 | [NAME] | ABC Company | PB | A | 2R | 0 | EA | 0.10665 |  | 0 |  |  |  | 0 | 56 |  |  | NO
C051 | [NAME] | ABC Company | PB | A | OBS | 0 | EA | 0.10665 |  | 0 |  |  |  | 0 | 56 |  |  | NO
C051 | [NAME] | ABC Company | PB | A | PLT | 0 | EA | 0.10665 |  | 0 |  |  |  | 0 | 56 |  |  | NO
C1036 | Bottle - Blue | XYZ Company | PB | A | 1 | 5560 | EA | 0.2627 |  | 219313.78 |  |  |  | 0 | 84 |  |  | NO |  |  | <<Desired Result
C1036 | Bottle - Blue | XYZ Company | PB | A | 1MR | 0 | EA | 0.2627 |  | 0 |  |  |  | 0 | 84 |  |  | NO
C1036 | Bottle - Blue | XYZ Company | PB | A | 1R | 0 | EA | 0.2627 |  | 0 |  |  |  | 0 | 84 |  |  | NO
C1036 | Bottle - Blue | XYZ Company | PB | A | 2 | 68000 | EA | 0.2627 |  | 0 |  |  |  | 0 | 84 |  |  | YES
C1036 | Bottle - Blue | XYZ Company | PB | A | 2MR | 0 | EA | 0.2627 |  | 0 |  |  |  | 0 | 84 |  |  | NO
C1036 | Bottle - Blue | XYZ Company | PB | A | 2R | 0 | EA | 0.2627 |  | 0 |  |  |  | 0 | 84 |  |  | NO
C1036 | Bottle - Blue | XYZ Company | PB | A | OBS | 0 | EA | 0.2627 |  | 0 |  |  |  | 0 | 84 |  |  | NO
C1036 | Bottle - Blue | XYZ Company | PB | A | PLT | 0 | EA | 0.2627 |  | 0 |  |  |  | 0 | 84 |  |  | NO
C1077 | Bottle - Amber | LMN, Inc. | PB | A | 1 | 12733 | EA | 0.10331 |  | 100000 |  |  |  | 310000 | 56 |  |  | NO |  |  | <<Desired Result
C1077 | Bottle - Amber | LMN, Inc. | PB | A | 1MR | 0 | EA | 0.10331 |  | 0 |  |  |  | 0 | 56 |  |  | NO
C1077 | Bottle - Amber | LMN, Inc. | PB | A | 1R | 0 | EA | 0.10331 |  | 0 |  |  |  | 0 | 56 |  |  | NO
C1077 | Bottle - Amber | LMN, Inc. | PB | A | 2 | 207500 | EA | 0.10331 |  | 0 |  |  |  | 0 | 56 |  |  | YES
C1077 | Bottle - Amber | LMN, Inc. | PB | A | 2MR | 0 | EA | 0.10331 |  | 0 |  |  |  | 0 | 56 |  |  | NO
C1077 | Bottle - Amber | LMN, Inc. | PB | A | 2R | 0 | EA | 0.10331 |  | 0 |  |  |  | 0 | 56 |  |  | NO
C1077 | Bottle - Amber | LMN, Inc. | PB | A | PLT | 0 | EA | 0.10331 |  | 0 |  |  |  | 0 | 56 |  |  | NO
Quantities Needed are still exclusively shown in Location 1
In the above example:
Item # C051 should return $0.00 in Column "U" (as the Qty needed 1,999,102 exceeds the Loc1+Loc2 QOH of 277,382)
Item # C1036 should return $0.00 in Column "U"  (as the Qty needed 219,314 exceeds the Loc1+Loc2 QOH of 73,560)
Item # C1077 should return $12,421.27 in Column "U"  (as Loc1+Loc2 QOH of 220,233 exceeds the [NAME] needed 100,000)
#C1007: |  |  |  |  |  | Exceeded by 120,233 * Unit Cost of $0.10331 = $12,421.27