Number Sequence for Warehouse layout

[simned01]

Help I am looking to do a warehouse layout, and want to assign a pallet location to products. However some products I can put 4 items into one location (0.25) and others only 2 (0.5) where most will have 1 product to 1 location (1). I need Excel to return a 1 or 0 based on if a new location is required. I have been through loads of nested 'IF's but can't the right result - I feel I am so close, but so far

[TABLE="width: 96"]


<colgroup><col style="width: 48pt;" span="2" width="64">
<tbody>[TR]

[TD="width: 64, bgcolor: transparent"]Data[/TD]

[TD="width: 64, bgcolor: transparent"]Desired Result[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent, align: right"]1[/TD]

[TD="bgcolor: transparent, align: right"]1[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent, align: right"]1[/TD]

[TD="bgcolor: transparent, align: right"]1[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent, align: right"]1[/TD]

[TD="bgcolor: transparent, align: right"]1[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent, align: right"]1[/TD]

[TD="bgcolor: transparent, align: right"]1[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent, align: right"]0.5[/TD]

[TD="bgcolor: transparent, align: right"]1[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent, align: right"]0.5[/TD]

[TD="bgcolor: transparent, align: right"]0[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent, align: right"]0.5[/TD]

[TD="bgcolor: transparent, align: right"]1[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent, align: right"]0.25[/TD]

[TD="bgcolor: transparent, align: right"]1[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent, align: right"]0.25[/TD]

[TD="bgcolor: transparent, align: right"]0[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent, align: right"]0.25[/TD]

[TD="bgcolor: transparent, align: right"]0[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent, align: right"]0.25[/TD]

[TD="bgcolor: transparent, align: right"]0[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent, align: right"]0.25[/TD]

[TD="bgcolor: transparent, align: right"]1[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent, align: right"]0.25[/TD]

[TD="bgcolor: transparent, align: right"]0[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent, align: right"]0.25[/TD]

[TD="bgcolor: transparent, align: right"]0[/TD]

[/TR]


</tbody>[/TABLE]

 

[XOR LX]

Hi,

Sorry if I'm not understanding, but I don't understand your desired results for the 0.25 Data. Shouldn't the first two be both zero? Which will then obviously change the next entries. Or am I missing something?

Regards

 

[shg]

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]

Data​

[/td][td]

Result​

[/td][td][/td][/tr]

[tr][td]

2​

[/td][td]

1​

[/td][td]

1​

[/td][td]B2: =CEILING(SUM(A$1:A2), 1) - SUM(B$1:B1)[/td][/tr]

[tr][td]

3​

[/td][td]

1​

[/td][td]

1​

[/td][td][/td][/tr]

[tr][td]

4​

[/td][td]

1​

[/td][td]

1​

[/td][td][/td][/tr]

[tr][td]

5​

[/td][td]

1​

[/td][td]

1​

[/td][td][/td][/tr]

[tr][td]

6​

[/td][td]

0.5​

[/td][td]

1​

[/td][td][/td][/tr]

[tr][td]

7​

[/td][td]

0.5​

[/td][td]

0​

[/td][td][/td][/tr]

[tr][td]

8​

[/td][td]

0.5​

[/td][td]

1​

[/td][td][/td][/tr]

[tr][td]

9​

[/td][td]

0.25​

[/td][td]

0​

[/td][td][/td][/tr]

[tr][td]

10​

[/td][td]

0.25​

[/td][td]

0​

[/td][td][/td][/tr]

[tr][td]

11​

[/td][td]

0.25​

[/td][td]

1​

[/td][td][/td][/tr]

[tr][td]

12​

[/td][td]

0.25​

[/td][td]

0​

[/td][td][/td][/tr]

[tr][td]

13​

[/td][td]

0.25​

[/td][td]

0​

[/td][td][/td][/tr]

[tr][td]

14​

[/td][td]

0.25​

[/td][td]

0​

[/td][td][/td][/tr]

[tr][td]

15​

[/td][td]

0.25​

[/td][td]

1​

[/td][td][/td][/tr]
[/table]

 

[simned01]

Hi,

Sorry if I'm not understanding, but I don't understand your desired results for the 0.25 Data. Shouldn't the first two be both zero? Which will then obviously change the next entries. Or am I missing something?

Regards

Sorry no. Each time you change for a number in the sequence i.e 1 to 0.5 or 0.5 to 0.25 then it needs to reflect a 1, and then at each multiple of 1 again need to reflect a 1. What I am trying to model is a square where you can either put 1 item in it (1) or two items (0.5) or 4 item (0.25) - you can't exceed the capacity of the square but you can for example have 3 x 0.25. Does that make sense.

Thanks for the prompt reply however

 

[simned01]

Thanks, but the answer does match the desired result. Thanks for the prompt response however

 

[simned01]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Data​

[/TD]
[TD]

Result​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD]B2: =CEILING(SUM(A$1:A2), 1) - SUM(B$1:B1)
[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

0.5​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

0.5​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

0.5​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

0.25​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

0.25​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

0.25​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

0.25​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]

0.25​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]

0.25​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]

0.25​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks, but the answer does match the desired result. Thanks for the prompt response however