Univers and Channels for pixels

cfer

Well-known Member
Joined
Jul 29, 2002
Messages
560
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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
ABCDE
1Channels usedstart universe #start channel #end universe #end channels #
2135111135
3380113625
Sheet1
 
Upvote 0
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
 
Upvote 0
Hi, here are one set of options you can try, formulas can be copied down as required.


Excel 2013/2016
ABCDE
1Channels usedstart universe #start channel #end universe #end channel #
2135111135
3380113625
4105262110
5021102110
6102021114110
7400411140
85541555
Sheet1
Cell Formulas
RangeFormula
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)
 
Last edited:
Upvote 0
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
 
Upvote 0
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.. :confused:
 
Last edited:
Upvote 0
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
 
Upvote 0
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
GHIJK
1Channels usedstart universe #start channel #end universe #end channel #
21????????
31????????
41????????
52????????
62????????
7135????????
8380????????
Sheet1
 
Upvote 0
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
 
Upvote 0
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 :confused:


Excel 2013/2016
ABCDE
1Channels usedstart universe #start channel #end universe #end channel #
211111
311212
411313
511414
621516
721718
8135191143
93801144213
Sheet1
Cell Formulas
RangeFormula
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)
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top