• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Binary Breakdown

P

Phuzzy

New Member
Okay. I have a sheet with a list of items that need to be categorized. An item can fall into more than one category. Other people will be categorizing them. I want to simplify the process for them. So I thought that it would be good to number the categories, then they can add up the numbers then later you can derive from the number what categories they chose.


For example:


Category Value

Widgets 1

Grommets 2

Dohickeys 4

Thingamabobs 8

Whatnots 16


So if an item was both a thingamabob and a widget, they would enter a 9. If it was a Dohickey and a grommet and a Widget, they would enter 7


Is there a formula I can use to deconstruct the value back to the Word Categories?


There might be an even better method that I have not thought of, so I am open to suggestions.


Thanks,

Phuzzy
 
Check out HEX2BIN.

Starting with this:

=HEX2BIN(7)

Gives:

111


You could then use the LEN and MID functions to figure out which categories were chosen.


Or, you could simply categorize things by using commas

1,2,4

This would be easiest, as you can clearly separate the values and lookup corresponding word.
 
Hi, Phuzzy!


Give a look at this file:

http://dl.dropbox.com/u/60558749/Binary%20Breakdown%20%28for%20Phuzzy%20at%20chandoo.org%29.xlsx


I'd rather choose to mark with "X" each category and not calculate which value enter for each item properties combination. I think it'll be easier for people who enter data and it'll lead to less errors.

Then apply SUMPRODUCT function to code it, and after that decode it with this formula:

=SI(EXTRAE(DERECHA(CONCATENAR("00000";DEC.A.BIN($G2));5);5-COLUMNA()+8;1)="1";"X";"") -----> in english: =IF(MID(RIGHT(CONCATENATE("00000",DEC2BIN($G2)),5),5-COLUMN()+8,1)="1","X","")


Just advise if any issue while implementing in actual columns.


Regards!
 
Just to clarify Luke's answer

=HEX2BIN(7)

Gives:

111

Which is technically 4,2,1
 
@Hui

Hi!

Good point, I made the very same mistake in my first attempt for the decoding formula "...COLUMN()-8..." and then when I saw the columns mirrored I blushed and changed to "...5-COLUMN()+8...".

You're getting slow, next time please advise earlier so as I can avoid making mistakes.

Regards!
 
@SirJB7: Unfortunatly my work has Dropbox, googledocs and other such sites blocked, so I cannot see your solution. Why they block them, I don't know, but it drives me nuts. Additionally, here at the office we are using Excel 2003. If I remember correctly, 2003 does not contain sumproduct.


@ Luke M: Very good suggestions. I will think more on this. I need to make this as simple as possible for the people involved.
 
Hi, Phuzzy!

OK, let me give a try replacing SUMPRODUCT by SUMIFS. Get back to you in a while (if I'm lucky and successful).

Any other method for sending you a file? Skydrive? Mail? If not, I'll try to copy here the formulas, they aren't a lot neither.

Regards!
 
Is there a way to send private messages to users on here? I'd send you my email address. Thanks SirJB7 for your assistance. It amazes me how fast you guys come up with solutions.
 
Hi, Phuzzy!


You make me doubt, and I should rectify what I posted wrongly before when I agreed with you.

SUMIFS function was added in 2007 version, SUMPRODUCT is available in 2003 version, so...

I don't have to change anything.


Here is the worksheet:

-----

[pre]
Code: 
Item	Wid	Gro	Doh	Thi	Wah	Cat	Wid?	Gro?	Doh?	Thi?	Wah?
a	X					1	X
b		X				2		X
c			X			4			X
d				X		8				X
e					X	16					X
f	X	X				3	X	X
g	X		X			5	X		X
h	X			X		9	X			X
i	X				X	17	X				X
J	X	X	X	X	X	31	X	X	X	X	X
[/pre]
-----


Formulas/Contents:


Column A : Item ID


Columns B:F : "X" (unquoted) if matchs type or blank if not


Column G: Category, formula

=SUMAPRODUCTO(--(B2:F2="X");2^(COLUMNA(B2:F2)-2)) -----> in english: =SUMAPRODUCT(--(B2:F2="X"),2^(COLUMN(B2:F2)-2))


Columns H:L: decoding of category from column G, formula

=SI(EXTRAE(DERECHA(CONCATENAR("00000";DEC.A.BIN($G2));5);5-COLUMNA()+8;1)="1";"X";"") -----> in english: =IF(MID(RIGHT(CONCATENATE("00000",DEC.2.BIN($G2)),5),5-COLUMN()+8,1)="1","X","")


Please try it and let's know the result.


Regards!


PS: I truncated the title row to 3 char, because it was harder to embed the correct spacing for the whole table. I still have problems even using backticks... maybe one day I'll learn it.
 
@Luke (because it's more probable that you'd be here than Hui)


Hi!


Am I wrong or there are a couple of posts missing in this topic? I can't find the one where I wrote my first suggestion with the formulas and the uploaded link. It should be between Hui's clarification about your formula and my answer to Hui's comment, or between Montrey's post and Hui's one, I don't remember exactly. I don't know if there are any more ones missing, but at least this yes.


Is there a way to check this out? Thanks.


Regards!
 
Hi, all!

I think that the missing post is only one: number 24.867.

Correct me if I'm wrong.

I think there's been a polish broom that swept too much...

Regards!
 
Hi, Phuzzy!

Glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top