Yesterday I have learned this cool excel charting trick and I cant wait to share it with you all.
The problem: I have too many charts & want to show one based on selection
You have made 3 charts to show your company performance in the last 8 years. But you don’t want to clutter the project report with all of them. You would rather want to show one chart and let user choose to see the any of the other two, like this:
The Solution: Use INDIRECT() and a nifty image hack
- First, create your charts in a separate worksheet like this (remember you need to create all 3 charts first)
- Once the charts are created adjust the width and heights of 3 cells and place one chart in each like above.
Now, go back to the sheet where you want to control the display, and define a new named range. Lets call it getChart. You can define new named ranges from menu > insert > name > define. You will see a dialog box like this (right):- In the “Refers to:” area we will now write an INDIRECT() spreadsheet formula to refer to one of the 3 cells where charts are placed. A sample formula is below:
IF('View them here'!$C$2="Sales",INDIRECT("'Place your charts here'!F11"),IF('View them here'!$C$2="Expenses",INDIRECT("'Place your charts here'!F12"),INDIRECT("'Place your charts here'!f13"))) - The above formula assumes, you are going to control chart display thru cell C2 in the sheet ‘view them here’
- Now adjust a cell’s size in this spread sheet to be big enough so that we can fit the selected chart.
- Go to Menu > Insert > Picture > From File and insert any picture. This is just for a placeholder purpose, so any picture would do, including that of your cat’s. 🙂
- Finally, select the image and go to formula bar and type
=getChart(or whatever name you gave to the named range), like this:
- Change the value in C2 and see the magic.
How this hack works?
In excel you can assign named ranges to images inserted in the sheet. So when you adjusted the cell sizes in the sheet with charts and created indirect references through INDIRECT() formula and used it in the named range, excel fetched the content of the cell (the chart) and replaced your cat’s picture with that. This powerful little trick can help you make interactive dashboards within little space.
Pretty cool, eh?
Download and see in action
Here is a link to the downloadable conditional chart display workbook. I have tested this in Excel 2003, but I guess it should work the same way in most of the modern versions of excel. Feel free to drop a comment if you see this not working in a particular version.
26 Responses to “Get busy this weekend, with OR XOR AND [Excel Homework]”
first solution for AND
The two numbers are in A1 and B1
= SUBSTITUTE (SUBSTITUTE (A1+B1*9*9, 9, 1), 8, 0)
regards
Stef@n
next solution for OR
=1*SUBSTITUTE (A1+A2;2;1)
regards
Stef@n
last solution for XOR
=1*SUBSTITUTE (A1+A2;2;0)
regards
Stef@n
Or you could make use of the VBA logical operators!
Define the following as custom functions
Public Function BITXOR(x As Long, y As Long)
BITXOR = x Xor y
End Function
Public Function BITAND(x As Long, y As Long)
BITAND = x And y
End Function
Public Function BITOR(x As Long, y As Long)
BITOR = x Or y
End Function
and then use them such:
A B =BITOR(A,B) =BITAND(A,B) =BITXOR(A,B)
0101 0100 0101 0100 0001
an another solution for AND
=1*SUBSTITUTE (SUBSTITUTE (A1+A2;1;0);2;1)
note:
the binary numbers are in A1 and A2 !
regards
Stef@n
I was obviously playing hooky at the beach during the bit-wise math lesson – you lost me at “Understanding bit-wise operations” 🙂
After looking at the above solutions, I find my solution silly, but still:
For the following formulae,
Row 1: headers,
Row 2: OR
Row 3: AND
Row 4: XOR
Column 1: Input 1
Column 2: Input 2
Column 3: Result
OR
{=SUM(IF(MID(A2,ROW(OFFSET($A$1,0,0,LEN(A2),1)),1)+MID(B2,ROW(OFFSET($A$1,0,0,LEN(B2),1)),1)>0,1,0)*10^(LEN(A2)-ROW(OFFSET($A$1,0,0,LEN(B2),1))))}
AND
{=SUM(IF(MID(A3,ROW(OFFSET($A$1,0,0,LEN(A3),1)),1)+MID(B3,ROW(OFFSET($A$1,0,0,LEN(B3),1)),1)=2,1,0)*10^(LEN(A3)-ROW(OFFSET($A$1,0,0,LEN(B3),1))))}
XOR
{=SUM(IF(MID(A4,ROW(OFFSET($A$1,0,0,LEN(A4),1)),1)+MID(B4,ROW(OFFSET($A$1,0,0,LEN(B4),1)),1)=1,1,0)*10^(LEN(A4)-ROW(OFFSET($A$1,0,0,LEN(B4),1))))}
@Anup
Please don't consider your solution silly
Firstly, You are the 3rd person to submit an answer
Secondly, The best formula/function is the one that you know and understand.
I think I have a very tedious solution, which people won't have the patience to do except in small numbers.
I used the same problem setup as "Anup Agarwal"
AND =IF(AND(MID(B2,1,1)="1",MID(C2,1,1)="1"),1,0)&IF(AND(MID(B2,2,1)="1",MID(C2,2,1)="1"),1,0)&IF(AND(MID(B2,3,1)="1",MID(C2,3,1)="1"),1,0)&IF(AND(MID(B2,4,1)="1",MID(C2,4,1)="1"),1,0)
OR =IF(OR(MID(B3,1,1)="1",MID(C3,1,1)="1"),1,0)&IF(OR(MID(B3,2,1)="1",MID(C3,2,1)="1"),1,0)&IF(OR(MID(B3,3,1)="1",MID(C3,3,1)="1"),1,0)&IF(OR(MID(B3,4,1)="1",MID(C3,4,1)="1"),1,0)
=IF(OR(AND(MID(B4,1,1)="1",MID(C4,1,1)="0"),AND(MID(B4,1,1)="0",MID(C4,1,1)="1")),1,0)&IF(OR(AND(MID(B4,2,1)="1",MID(C4,2,1)="0"),AND(MID(B4,2,1)="0",MID(C4,2,1)="1")),1,0)&IF(OR(AND(MID(B4,3,1)="1",MID(C4,3,1)="0"),AND(MID(B4,3,1)="0",MID(C4,3,1)="1")),1,0)&IF(OR(AND(MID(B4,4,1)="1",MID(C4,4,1)="0"),AND(MID(B4,4,1)="0",MID(C4,4,1)="1")),1,0)
Sorry my last post was totally messed up
AND
=IF(AND(MID(B2,1,1)="1",MID(C2,1,1)="1"),1,0)&IF(AND(MID(B2,2,1)="1",MID(C2,2,1)="1"),1,0)&IF(AND(MID(B2,3,1)="1",MID(C2,3,1)="1"),1,0)&IF(AND(MID(B2,4,1)="1",MID(C2,4,1)="1"),1,0)
OR
=IF(OR(MID(B3,1,1)="1",MID(C3,1,1)="1"),1,0)&IF(OR(MID(B3,2,1)="1",MID(C3,2,1)="1"),1,0)&IF(OR(MID(B3,3,1)="1",MID(C3,3,1)="1"),1,0)&IF(OR(MID(B3,4,1)="1",MID(C3,4,1)="1"),1,0)
XOR
=IF(OR(AND(MID(B4,1,1)="1",MID(C4,1,1)="0"),AND(MID(B4,1,1)="0",MID(C4,1,1)="1")),1,0)&IF(OR(AND(MID(B4,2,1)="1",MID(C4,2,1)="0"),AND(MID(B4,2,1)="0",MID(C4,2,1)="1")),1,0)&IF(OR(AND(MID(B4,3,1)="1",MID(C4,3,1)="0"),AND(MID(B4,3,1)="0",MID(C4,3,1)="1")),1,0)&IF(OR(AND(MID(B4,4,1)="1",MID(C4,4,1)="0"),AND(MID(B4,4,1)="0",MID(C4,4,1)="1")),1,0)
@stefan,
I just couldn't get your solutions to work.
01010101010 + 01010101110 = 02020210120
what am i doing wrong?
@anup
...I got yours to work!
@Stephen - I get the same, but Stef@an's second solution for AND does work (at least for the test cases I used)
@ Stephen / Rich
yes , you are right ! - only this works:
OR
=1*SUBSTITUTE (A1+A2;2;1)
XOR
=1*SUBSTITUTE (A1+A2;2;0)
AND
=1*SUBSTITUTE (SUBSTITUTE (A1+A2;1;0);2;1)
@Stef@n - You're answer is really smart, I never knew about the substitute function before. Great Work!
Thx Michael 🙂
yes - it is simply easy 😉
if you add 1 and 1 - excel calculate 2
and then you have to substitute the 2 - new = 0 respectively 1
Here is a good resource for people wanting to learn binary and hexadecimal.
http://justwebware.com/bitwise/bitwise.html
Three that weren't asked for:
NOT
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1+A2,0,3),1,0),3,1)
EQV
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1+A2,0,3),2,3),1,0),3,1)
IMP
=SUBSTITUTE(SUBSTITUTE(A1+SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,0,3),1,0),3,1),0,1),2,0)
(was using Daniel Ferry's bitwise file to verify against)
@ Kyle
Not only takes one parameter and inverts 0 -1 and 1-0
Took out the +A2
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,3),1,0),3,1)
Great solutions!
I'll add two:
NAND =1*SUBSTITUTE (A1+A2,2,0)
NOR=1*SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(A1+A2,0,2),1,0),2,1)
This will work for binary numbers of any size (although the text format mask will have to have as many zeroes as there are digits in the longest addend)
Assume binary #s are in C35 & C36, then add and format as text in C37:
=TEXT(C36+C35,"000000000000")
-sum- = 101112211112
AND - SUBSTITUTE 0s for 1s in -sum-, then sub 1s for 2s
=SUBSTITUTE(SUBSTITUTE(C37,"1","0"),"2","1")
OR - sub 1s for 2s in -sum-
=SUBSTITUTE(C37,"2","1")
XOR - sub 0s for 2s in -sum-
=SUBSTITUTE(C37,"2","0")
Just wandered by:
AND:
=SUBSTITUTE(A1+A2,1,0)/2
Clever, Shane. I like that.
[…] post http://www.excelhero.com/blog/2010/01/5-and-3-is-1.html for examples using Sumproduct, and http://chandoo.org/wp/2011/07/29/bitwise-operations-in-excel/ for examples using Text […]
Hi Chandoo,
I am not (yet) really into bitwise calculation, but I am looking for a way to speed up my vba calculation with very big numbers. Would is ben convenient to use bitwise notation for this?
Best regards,
Ronald (the Netherlands)
p.s. love your country!
@Ronald
I'd suggest asking this in the Chandoo.org Forums
https://chandoo.org/forum/
Attach a sample file with an example of some data and describe what you want to achieve