Complex Bin range for RGB Histogram

I think the way I would do it would be to assign each RGB bin a 3 digit number. Maybe bin 1 is assigned 000, bin3 is 010, bin 8 is 111.
For each data point, compute the equivalent 3 digit number. Something like 100*int(column1/128)+10*int(column2/128)+1*int(column3/128), should yield the correct 3 digit number based on the numbering scheme given. From there, you can use the usual histogram creating tools (FREQUENCY() function followed by a column chart).

I'm sure there are other approaches that would work equally well. This one made sense to me.

One way:

Row\Col	A	B	C	D	E
1	R	G	B	Bin
2	123	23	34	0	D2: =SUMPRODUCT({4,2,1}, INT(A2:C2/128))
3	23	43	223	1
4	12	34	56	0
5	231	32	112	4
6	123	12	42	0
7	23	233	44	2
8	223	34	23	4
9	33	26	29	0
10	255	255	255	7

Picture aren't of much use.

Using the formula from the prior post,

Row\Col	A	B	C	D	E	F	G	H
1	R	G	B	Bin		Bin	Count
2	123	23	34	0		0	4	G2:G9: {=FREQUENCY(D2:D9, $F$2:$F$9)}
3	23	43	223	1		1	1
4	12	34	56	0		2	1
5	231	32	112	4		3	0
6	123	12	42	0		4	2
7	23	233	44	2		5	0
8	223	34	23	4		6	0
9	33	26	29	0		7	0

No; that's what the FREQUENCY function does. See Help.

Select G2:G9, paste the formula in the formula bar (minus the curly braces), press and hold the Ctrl and Shift keys, then press Enter.

Why would you want non-numeric bins?

And why do you want to use Histogram instead of FREQUENCY?

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

http://www.mrexcel.com/forum/excel-q...-function.html

I completely understand, but I did not receive a single reply from the other site, and I felt I was not making headway in this forum. Look at it this way: had I received a solution from either site I would have posted it on the respective forum. That is proper forum etiquette. I understand why you would want to see if someone in another forum is actively helping, and what advice they have already given. Point taken. My horse is dead..and beaten.

Originally Posted by ibcoding

Here is a photo of what I have.

Again, pictures aren't of much use.

Bins must be a 1D array, and I can't tell what you're trying to calculate, and I'm not going to enter data manually from a picture to try to figure it out.

You might have better luck if you would actually try the suggestions that have been made, or explain why they are not appropriate and you're going off in your own direction. This is not a complex problem.

How about SUMPRODUCT:



Untitled.png

That's it actually. I didn't think to use SUMPRODUCT. So I'll just have to input the RGB bin values manually, which is better than 512 formulas. 512 inputs here I come!. Thank you.

Originally Posted by ibcoding

So I'll just have to input the RGB bin values manually. 512 inputs here I come!.

Formula can do it automatically, I think. What is the logic? Can you give more sample bins, i.e from bin 9 to 16?

I tried the suggestions but they do not work.

When I tested mine, it seemed to work. I expect that shg tested his and it worked, too. I think what we need to do is figure out why you are having trouble implementing our suggestions.

I suspect that this:

will not let me choose the bin ranges with non-numeric values.

might be part of it. When I tested mine, I made sure that the assigned bin values would be numeric to avoid any troubles with text strings and numbers stored as text strings. Remember that, to a computer, the number 0 is the same as 00 is the same as 000. As long as your algorithm maintains this equality, it should not matter that Excel shows 0 when you enter 000. If you want to display all three digits, including leading 0's, use a number format code like "000".

If you are going to want to use smaller divisors (64 or 32), I would suggest you decide at the start what your smallest division will be, then build from there.

Here's my test spreadsheet. See if it helps understand how this might work.

After reading additional discussion. You shouldn't need to manually input the RGB bins. You just need to apply the same logic/formulas to the bin cutoff values as was used for the real RGB points.

Rows 13:20 are the data that you posted in post #1. Rows 21 on are some random numbers I generated (see the RANDBETWEEN() function in R21C1:R41C3). The FREQUENCY() function in column 6 is limited to the data you posted from post 1 and shows that it creates the result you expected. The FREQUENCY() function in column 7 shows the expanded results for your data plus the random data I generated.

Originally Posted by ibcoding

Ok, I am just not familiar with Excel. This is my first time using this darn application. How do I alter the calculations to take in massive amounts of RGB data. I know how to replace the rows starting at R13C2 (Ctrl+Shift+DownArrow) which selects everything from that point down.

Does this also mean that this is your first attempt at any spreadsheet application? One of the first things I learned when I first started learning spreadsheets (since almost all spreadsheet applications are the same in this regard) is how to create formulas so that they can easily and efficiently be copied. I would suggest you start with something like this: https://support.office.com/en-us/art...3-c311f47ca173 and/or any other "introduction to spreadsheets" tutorial you can find on the internet. The key concepts you need to understand are those related to relative and absolute referencing and how to write formulas that are easy to copy. For example, the formulas I used in the "intermediate calculations" section should just copy+paste down as far as you need them to go. IMO, a big part of what makes spreadsheets useful is this ability to create a formula once, then copy it wherever it is needed. Learning how this part of spreadsheet programming works is an important first lesson to understand.

ctrl+shift+enter is the keystroke for entering array formulas, which is not really the same as copy and paste. When I wrote the formula for the intermediate calculations, I created/entered the formula in R13C5. Then I copied R13C5 to the clipboard (ctrl+c), then selected the desired destination range, then pasted the formula in the destination range (ctrl+v). Copy and paste commands are also available in the Clipboard group of the Home tab on the ribbon, and by right clicking on a cell or range to bring up the context menu.

The problem is that I can't seem to make the formula repeat without manually copying and pasting it into each cell.......NEVERMIND.. Ctrl+Enter

Row\Col	B	C	D	E	F	G	H
2					nBins	8	<< change this
3
4	R	G	B		Bin	Count
5	58	229	2		0	55
6	242	231	244		1	60
7	63	141	236		2	68
8	178	109	212		3	68
9	38	67	230		4	71
10	29	165	45		5	66
11	124	75	47		6	65
12	228	136	43		7	59
13	166	224	139		8	#N/A
14	155	219	211		9	#N/A
15	60	246	16		10	#N/A
16	134	30	72		11	#N/A
17	14	44	184		12	#N/A
18	217	159	122		13	#N/A

See attached. All you need to do is paste your actual data starting in B5.

You're welcome.