Univers and Channels for pixels

[cfer]

Hi, I am wanting to put together a spreadsheet that will calculate my pixel led lights into universes.

Each Universe has 510 channels, each Universe increments by 1, you can hundreds of universes.

Headings will be

Channels used (A), start universe # (B), start channel # (C), end universe # (D), end channels # (E)

To start with I will start with Universe 1, Channel 1

So if I allocate 135 channels in cell A2

B2 = 1
C2 = 1
D2 = 1
E2 = 135

If I then allocate 380 channels to A3, then

B3 = 1
C3 = 136
D3 = 2
E3 = 5

The total amount for a Universe is 510, as I exceeded the 510 limit by 5, I have to increment to the next universe (2), and start at one again, as I have 5 channels left over, the end channel is 5.

If I then add another 200 channels, I would start at Universe 2, channel 6, ending universe 2, channel 205,

I need to keep this all the way down, the spread sheet.

Any suggestions very much appreciated

Cfer

 

[Scott Huish]

Here is the sample you gave, but I don't understand what math you use to get the number for each column after the value you put in Column A. Can you please clarify?

Excel 2010
	A	B	C	D	E
1	Channels used	start universe #	start channel #	end universe #	end channels #
2	135	1	1	1	135
3	380	1	136	2	5
Sheet1

 

[cfer]

Hi Scott, thanks for having a look.

It is a bit hard to get ones head around pixel universes.

Basically one universe accounts for 510 channels

Channels 1 to 510 are in Universe 1

Channels 511 to 1021 are in Universe 2

Channels 1022 to 1532 are in Universe 3

Channels 1533 to 2143 are in Universe 4

The channels numbers are consecutive.

Remember that the most channels in a Universe is 510

A universe could be referenced in Cell F1 if required.

In the above example, I used 135 channels, I start with Channels 1 and Universe 1, as the channel number does not exceed 510, it ends in Universe 1.

The next line has 380 channels, which means the start number is at the end of the previous end channel, which next consecutive number is 136.

This part is the kicker, because the sum of 135 channels and 380 channels exceed 510 ( a Universe) by 5 channels, Universe 1 needs to be filled to the maximum of 510, that means the overflow of 5 channels need to go to the next Universe 2 were 510 channels are allocated from number 1..

Each universe needs to be filled with channels of 510, if the channels exceed the 510 limit, the excess get allocated to the nest Universe starting at 1.

This goes on indefinitely. Hope it makes sense

Thanks Cfer

 

[FormR]

Hi, here are one set of options you can try, formulas can be copied down as required.

Excel 2013/2016
	A	B	C	D	E
1	Channels used	start universe #	start channel #	end universe #	end channel #
2	135	1	1	1	135
3	380	1	136	2	5
4	105	2	6	2	110
5	0	2	110	2	110
6	1020	2	111	4	110
7	400	4	111	4	0
8	55	4	1	5	55
Sheet1

Cell Formulas
Range	Formula
B2	=MAX(1,ROUNDUP((SUM(A$2:A2)-A2)/510,0))
C2	=MOD(SUM(A$2:A2)-A2,510)+(A2>0)
D2	=MAX(1,ROUNDUP(SUM(A$2:A2)/510,0))
E2	=MOD(SUM(A$2:A2),510)

 

[cfer]

HI FormR,

Thank you for the quick reply, I entered the formulae intro the spreadsheet.

All seemed to work until I put only 1 channel in column A, which resulted in start channel and end channels being both the same number, whereas the end channel should have gone up by one.

Nearly there.

Thanks Cfer

 

[FormR]

All seemed to work until I put only 1 channel in column A, which resulted in start channel and end channels being both the same number

Hi, that result would seem to be consistent with your other examples. i.e. a number of 135 in column A has 135 in the end channel..

 

[cfer]

Hi FormR,

Thanks again, yes, you are right, that the first line has 135 as the end channel,, but it is the start of the channel count, which means from then on, the count must go up by one higher than the channels used.,

So if line 3 had only 1 channel used it must start at 136 and end on 137.

Sorry for the confusion

Cfer

 

[FormR]

So if line 3 had only 1 channel used it must start at 136 and end on 137.

Hi, it's still a little confusing.

Can you complete the table below with your expected results?

Excel 2013/2016
	G	H	I	J	K
1	Channels used	start universe #	start channel #	end universe #	end channel #
2	1	??	??	??	??
3	1	??	??	??	??
4	1	??	??	??	??
5	2	??	??	??	??
6	2	??	??	??	??
7	135	??	??	??	??
8	380	??	??	??	??
Sheet1

 

[cfer]

Hi FormR,

Thanks for helping out on this.

I could not get a copy of excel to enter into forum

The best I could do is paster

Hopefully it is readable. I followed your example from your post.

Channels used start universe # start channel # end universe # end channel #
1 1 1 1 1 1
2 1 1 2 1 2
3 1 1 3 1 3
4 1 1 4 1 4
5 2 1 5 1 6
6 2 1 7 1 8
7 135 1 9 1 144
8 380 1 145 2 15


Thanks Cfer

 

[FormR]

Hi, I think you made a mistake on the highlighted cells below - but for the rest, and specifically the cases where the "channels used" are "1", the suggested formulas return the results you now say you expect

Excel 2013/2016
	A	B	C	D	E
1	Channels used	start universe #	start channel #	end universe #	end channel #
2	1	1	1	1	1
3	1	1	2	1	2
4	1	1	3	1	3
5	1	1	4	1	4
6	2	1	5	1	6
7	2	1	7	1	8
8	135	1	9	1	143
9	380	1	144	2	13
Sheet1

Cell Formulas
Range	Formula
B2	=MAX(1,ROUNDUP((SUM(A$2:A2)-A2)/510,0))
C2	=MOD(SUM(A$2:A2)-A2,510)+(A2>0)
D2	=MAX(1,ROUNDUP(SUM(A$2:A2)/510,0))
E2	=MOD(SUM(A$2:A2),510)