Technical Discussion
  >> Windows Issues


Register (or login) on our website and you will not see this ad.


  Print Thread
Standard User camieabz
(sensei) Wed 03-Mar-21 17:47:20
Print Post

Binary to solve Decimal Excel (solution)


[link to this post]
 
I've playing with a spreadsheet recently, and was trying to setup a dynamic list of items for a chart.

I have ten possible chart data points, but while six are guaranteed, four are not, and one always appears last on the list. So imagine this example:

https://i.ibb.co/5jMrjvp/pic1.png

The top section shows all the sales people, plus 'temp sales people', who are never known from one period to the next. The bottom section shows a given period, with the unnamed or zero values removed. Long story short, creating IF statements to solve all possible combinations of those four unknowns was a headache.

Then I noticed while testing the following IF statement:
=IF(Person6 sales>0,1,0)


I ended up testing lots of 1s or 0s, and there was only the four of them. So it was easier to create a four digit number from the results with CONCATENATE and then convert the answer to decimal, with results ranging from 0 to 15.

Then it was very simple, with IF(OR([decimal answer]=15,14,13,etc, 1, 0). Or if you prefer the real formula (for the column where person 9 appears in the bottom section):

=IF([checklength]=1,Charity],

IF([checklength]=0,"",

IF(OR(Decimal=15,Decimal=14,Decimal=13,Decimal=12),Person7,

IF(OR(Decimal=11,Decimal=10,Decimal=7,Decimal=6),Person8,Person9))))


I realise that my journey to solve this was long-winded, and this post is probably more confusing than explanatory, but suffice it to say, by using 1s and 0s in IF statements, and using CONCATENATE of the various 1s and 0s into a binary word, and conversion to decimal, it can make for less complicated IF statements, if appropriate use of AND and OR statements are carefully thought out.

Apparently AND and OR statements can have up to 255 conditions, so it's proper binary. smile

Just putting it out there. Sometimes nested IFs are a complete PITA, and after several days of taking half an hour then walking away annoyed, I got it cracked. I'm sure there's a more simple way to do it all, but I don't know vba or pivot tables, so if there's a more simple way with formula alone, I'm happy to hear it. smile

P.S. - Excel 2007, so no fancy 365 solutions pls. tongue
Standard User ian72
(eat-sleep-adslguide) Thu 04-Mar-21 10:35:25
Print Post

Re: Binary to solve Decimal Excel (solution)


[re: camieabz] [link to this post]
 
Would need to really see the full example to properly work out what you are doing, but I think you could handle the binary slightly more simply. Instead of creating binary then converting to decimal you could create a binary string.

So, to create the string I believe you could essentially say:
=concatenate(if(Person 6 sales>0,"1","0", if(Person 7 sales>0,"1","0",if(Person 8 sales>0,"1","0",if(Person 9 sales>0,"1","0"))))

If I've typed that right then in your example you would have a binary string that says "0101".

Then you could use string functions to check if the binary digit is turned on. For example
IF(mid([decimal answer],1,1)="1"

I think this would simplify it but not 100% certain as I can't see your full code as to how you are getting to the decimal result.
Standard User ian72
(eat-sleep-adslguide) Thu 04-Mar-21 10:43:14
Print Post

Re: Binary to solve Decimal Excel (solution)


[re: ian72] [link to this post]
 
Just found another option. There is a bitand function in Excel (not sure when it was introduced but assuming it is in your version). With this you can take your decimal value and directly check to see if a bit is one or 0.

For example, if you had 5 (0101 in dec) then you can use:
=bitand(1,5). Returns 1 because the least sig bit is on
=bitand(2,5). Returns 0 because the second least sig bit is off
=bitand(4,5). Returns 1 because the third least sig bit is on
=bitand(8,5). Returns 0 because the most sig bit is off

You shouldn't even need to mess with converting from binary to decimal as you can just add the binary value to your sum as you go (ie for 1st it is 1, second is 2, third is 4, fourth is 8, etc, etc).


Register (or login) on our website and you will not see this ad.

Standard User camieabz
(sensei) Thu 04-Mar-21 16:31:47
Print Post

Re: Binary to solve Decimal Excel (solution)


[re: ian72] [link to this post]
 
In reply to a post by ian72:
So, to create the string I believe you could essentially say:
=concatenate(if(Person 6 sales>0,"1","0", if(Person 7 sales>0,"1","0",if(Person 8 sales>0,"1","0",if(Person 9 sales>0,"1","0"))))
Yeah, that would work too. That effectively does away with the four plus one cell to just one. smile

In reply to a post by ian72:
Then you could use string functions to check if the binary digit is turned on. For example
IF(mid([decimal answer],1,1)="1"

The thing is the cells are designed to shift up if person 'x' has zero. If you put your solution into each column to allow for person 'x' to appear, it duplicates person 'x' across multiple columns. While the IFs for the columns under persons 6 and 9 were quite simple, I drove myself batty with 7 & 8 (especially the former).

Bear in mind that each column's calc has to allow for others:

Column 6: person 6, or person 7, or person 8, or person 9, or failing all that, Charity. (never blank) Very easy. As long as your IF statement handles them in that order, it just works.

Column 9: Person 9, Charity or Blank. Very easy.

Column 8: person 8, or person 9 or Charity or blank. Fairly straightforward if column 7 is working, and you can see your solutions generating properly.

Column 7: If column 6 has person 6, then if person 7>0, person 7, but if person 7=0, then person 8, or person 9 or Charity.
-----

The thing is, if person 6 has zero, then person 7 moves to the left, and so should the rest, which means you have to add conditions to each cell based on another, based on another. Just saying 'be 1' ignores the state of the other cells and their new positions.

Ergo, it seemed more simple to have the binary word solution, which lays out the 16 possible permutations and tells the cells how to act if permutations 1-4 pop up, how to act in the next 5, and to be blank the rest of the time (if applicable).

Edit: Getting captchas, due to code in posts most likely, and didn't proof read properly.

Edited by camieabz (Thu 04-Mar-21 16:35:45)

Standard User ian72
(eat-sleep-adslguide) Mon 08-Mar-21 10:23:02
Print Post

Re: Binary to solve Decimal Excel (solution)


[re: camieabz] [link to this post]
 
Afraid I am having difficulty trying to visualise how you have all the formulas for it so difficult to make any suggestions.
Standard User camieabz
(sensei) Mon 08-Mar-21 13:15:05
Print Post

Re: Binary to solve Decimal Excel (solution)


[re: ian72] [link to this post]
 
Example uploaded:

http://s000.tinyupload.com/index.php?file_id=7551521...
Standard User ian72
(eat-sleep-adslguide) Mon 08-Mar-21 13:54:29
Print Post

Re: Binary to solve Decimal Excel (solution)


[re: camieabz] [link to this post]
 
Having a quick play there are simplifications that could be made.

You don't need to use bin2dec at all. Rather than in S6 to V6 using 1's set them to their binary value instead, so S6 would be set to 8, T6 to 4, U6 to 2 and V6 to 1. Then in T7 you just sum the 4 cells which gives you the same result without using S7 at all and without using bin2dec conversion.

The other big change I would make it in K7/K8.
K7 = =IF(Q8=1,N4,IF(Q8=0,"",IF(T7>=12,K4,IF(U6=2,L4,M4))))

This gets rid of both OR statements. Your first OR statement for T7 is essentially just saying if greater than 12. The second is just checking to see if L5 > 0 which you can do either by the way I have done it or by just doing it directly if you wanted.

The same change then gets made to K8.
=IF(Q8=1,N5,IF(Q8=0,"",IF(T7>=12,K5,IF(U6=2,L5,M5))))

If you wanted to continue the binary theme then rather than the U6=2 you could do a bitand function.
=IF(Q8=1,N4,IF(Q8=0,"",IF(T7>=12,K4,IF(bitand(2,T7),L4,M4))))But, that isn't really necessary given you have simpler ways to do it.
But, that isn't really necessary given you have simpler ways to do it.

Those are the most obvious simplifications I can think of from what you have. It is an interesting solution to a somewhat thorny problem.
Standard User camieabz
(sensei) Mon 08-Mar-21 16:23:39
Print Post

Re: Binary to solve Decimal Excel (solution)


[re: ian72] [link to this post]
 
In reply to a post by ian72:
It is an interesting solution to a somewhat thorny problem.


I think I pulled enough hair to stuff a small cushion, over several days. It seemed every time I had a solution, when I started testing it, I'd get "Sales 8" in a column and "Sales 8" in the next one as well.

It got me to thinking, that either I have very elaborate IF statements, or I work out a way of each possible combination having it's own value (hence the binary). I guess I was lucky it was just four columns. Had it been five, the possible combinations would have been far worse. smile
  Print Thread

Jump to