Complex Multiple Condition Excel Formula

Question

I need to write a complex formula in excel (or if someone has a suggestion as to another program to use I'm open to it!) with multiple conditions based on where the item is stored.

Each item has a minimum and maximum par level calculated, but can be stored in multiple locations. The percentage of that par is calculated based on where that item is stored (See last image below). For example:

Item A is stored in Central location 1, Central location 2, and 2 External (aka non-central) locations. There is a total quantity of 100 Item A's.

Based on our scenarios, we would find that:

Central Location 1: 70%
Central Location 2: 20%
External Location 1:10%/# of external Locations
External Location 2:10%/# of external Locations

So our par level for that item in each location would be:
Central Location 1: 70 of Item A
Central Location 2: 20 of Item A
External Location 1: 5 of Item A
External Location 2: 5 of Item A

The left side are the storage locations for each item ID #. I need to distribute the total Min and Max to each location depending on the scenarios below

I could go through and do this manually for each item (Where is stored, what is the scenario, calculate) but there are 1,500 items all stored in various places. Is there any formula I could write to calculate where the item is and how much of the item would go to each area its stored in?

I've tried using various IF and matching functions but feel like I don't have any clue where to start.

Any help would be great!

Different Scenarios of where an item can be stored. Depending on storage locations, each location will get a different percentage of the total (to the right in the image above)

Answer 1

OPTION 1

Build out your reference table like above using the following formulas for columns M and N row 2 and copy down for as many items as you have

=SUMPRODUCT(($C$2:$C$11=$J2)*(LEFT($A$2:$A$11)="c"))

=SUMPRODUCT(($C$2:$C$11=$J2)*(LEFT($A$2:$A$11)="e"))

After that in your cell M2 using the following equation and copy down:

=IF($A2="external",IF(INDEX($J$2:$N$5,MATCH($C2,$J$2:$J$5,0),4)=1,0.2,0.1)*INDEX($J$2:$N$5,MATCH($C2,$J$2:$J$5,0),2)/INDEX($J$2:$N$5,MATCH($C2,$J$2:$J$5,0),5),IF(--RIGHT($A2)=1,IF(INDEX($J$2:$N$5,MATCH($C2,$J$2:$J$5,0),4)=1,IF(INDEX($J$2:$N$5,MATCH($C2,$J$2:$J$5,0),5)=0,1,0.8),0.7)*INDEX($J$2:$N$5,MATCH($C2,$J$2:$J$5,0),2),IF(INDEX($J$2:$N$5,MATCH($C2,$J$2:$J$5,0),5)=0,0.3,IF(INDEX($J$2:$N$5,MATCH($C2,$J$2:$J$5,0),4)=1,0.8,0.2))))

in order to get your max values, repeat the concept but change

*INDEX($J$2:$N$5,MATCH($C2,$J$2:$J$5,0),2)

to

*INDEX($J$2:$N$5,MATCH($C2,$J$2:$J$5,0),3)

The change tells it to grab the value from the 3rd column instead of the second. There will be multiple locations to do this.

OPTION 2

Build out your table on the right to look like the following using the formula beneath the picture.

Build out columns M and N as OPTION 1 Build out columns O:Q using the following:

=IF(AND(M3=0,N3=0),0,IF(M3=2,0.7,IF(N3=0,1,0.8))*K3)
=IF(AND(M3=0,N3=0),0,IF(M3=2,IF(N3=0,0.3,0.2),0.8)*K3)
=IF(AND(M3=0,N3=0),0,IF(M3=1,0.2,0.1)*K3/N3)

Repeat these fomulas in R:T changing K3 to L3

Then in column D and E use the respective formulas:

=INDEX($O$3:$Q$6,MATCH($C2,$J$3:$J$6,0),MATCH($A2,$O$2:$Q$2,0))
=INDEX($R$3:$T$6,MATCH($C2,$J$3:$J$6,0),MATCH($A2,$R$2:$T$2,0))

copy the formulas down as required.

OPTIONS 2 while more spread out is probably easier to read and thus maintain.