19 Sep 2014

How to use Event Viewer (Windows 7) to troubleshoot issues

I know Windows 7 has been here for some years ago, and I should have written this post some years backup. Nevertheless, I thought it would help many of my other less-techie friends. When Microsoft designed Windows 7's Event Viewer , it was meant to provide the user with more insightful, meaningful, and well-documented events to help better in troubleshooting. For some weeks, my laptop has been taking a long time than usual to boot and hence I take this opportunity to write this post, to test out Event Viewer.

Note: Technical inputs / help taken from Wiki, Google and Microsoft.

Use Android phone as Wifi repeater

Have you often felt that you are out of wifi range, when you decide to take your laptop in the following area or garden? Maybe their router doesn't cover the whole of your home, and you feel the need to extend the range. There are lots of ways to extend the Routers range, however one way you can extend is by making use of your Android device as one Wi-Fi repeater, also known as a Wi-Fi extender.

Some less known smart tools / gadgets

I am a gadget freak, always ready to try out new things in the market. Off late i have been using some of them, which i like and would like to share. Below are few of the tools, which i use regularly.

18 Sep 2014

Install incompatible‘ apps on Android*** ROOT required

Occasionally, you may have for sure known or seen that; despite having a new, powerful phone running the latest version of Android, some of the high end games are not compatible with your device !!

Not only games, you find that some of the apps provided in Google Play are not compatible to be installed on your phone. To circumvent that, we can force the incompatible app to operate on Android by editing the build.prop system file. You may wonder why Google Play is not allowing you to install the app !! A simple reason to start with is the app perhaps is tested to run on a limited handful of devices as the developer doesn't have the resources to provide wider support.

17 Sep 2014

Hack in-app purchases with Lucky Patcher 4.7

I have already introduced this tool in my earlier post here. Lucky Patcher is a powerful Android tool to remove ads, modify Permissions, bypass premium applications license verification, and more.
To use all features to Lucky Patcher, you will need a rooted device.

With the latest update in Lucky Patcher, you wont require to install or use freedom apk to hack in app purchases. The latest version of lucky patcher apk i.e. 4.7.0 supports 'Hack google in app emulation'.
Click here towards read in detail and to download the tool.

13 Sep 2014

Sum cells based on background color

I had this difficult task was of adding the contents of certain cells based on their background colors. This is a really tough, just thingking of it. I tried to do it with conditional formatting and formulas, but in vain. Then i came up with a clean trick that help me complete the same.

Let me help you illustrate with an example, the problem statement. To make the task more clearly, let us have a look at the image below.

As this image shows that here we don't need the sum total of all elements, but instead want the sum of the elements that have the same background color.

Recommended reading: Weighted sum in Excel

We know that the SUMIF function is a combination of the sum and if formula, and hence SUMIF can come quite handy for adding color based cells.

If you don't know how to use the SUMIF function, so before I go any further I strongly suggest you to read this post.

First of all let's try to understand how we do this:

Consider that we have a table as shown in the image below.

Then, we will add a column to this table where we will manually enter the background colors of its adjacent cells as shown in the image below.

Now, we tried to use SUMIF formula to find the sum of the cells with yellow background, like: = SUMIF (B2: B13, "Yellow", A2: A13)

Similarly, to find the sum of Orange and green, background cells will use the formulas = SUMIF (B2: B13, "Orange", A2: A13) e = SUMIF (B2: B13, "Green", A2: A13) respectively.

But, as we can see that this method is rather complicated, especially if we have to use this on lists with hundreds of elements.

So what's the alternative faster?

To make the above process, easier to use we need to reduce the effort of writing the cell background colors manually.

So, for this task we can use a small user-defined function (UDF), which will do the trick for us.
ColorIndex function (CellColor as range) ColorIndex = CellColor.Interior.ColorIndexEnd function
Note: This function does not return the color name, but returns the color index which is also a unique value and can be used in our task.

Follow the steps below to use the UDF:

1. first Open your spreadsheet where you need to add the cells based on background colors.

2. then, press ALT + F11 to open the VB Editor. navigate to ' Insert ' > ' module '.

3. after that, paste the "ColorIndex" UDF in the Editor.

4. now, add a column next to the range that you want to summarize. In this new column, enter the formula as:

ColorIndex = ()

5. after that, drag this formula to the entire range.

6. now, you can use the SUMIF function to add cells that have the same background color, as shown in the image above.

The UDF that will use this method is simply an extension of the function used above.

This function is like below:
Function SumByColor (CellColor as Range, rRange as Range) Dim cSum As LongDim ColIndex as IntegerColIndex = CellColor.Interior.ColorIndexFor each cl in rRange if cl.Interior.ColorIndex = cSum then ColIndex = WorksheetFunction.SUM (cl, cSum) End IfNext clSumByColor = cSumEnd function
How to use this UDF:

Follow the steps below to use this function:

1. open your destination worksheet.

2. press ALT + F11 to open the VBA Editor and navigate to ' Insert ' > ' module '.

3. copy the "SumByColor" function in the Editor.

4. now, simply enter the "SumByColor" function to call him and pass the following arguments:

= SumByColor (, )

Note:In the formula above the image instead of the first argument "A2" we could also have used any of the A2, A5, A8, A10, A12. Because all these cells have a yellow background.

In our case we can use the following formulas:
Sum of yellow cells: = SumByColor (A2, A2: A13) [as ' A2 ' is the address of the cell yellow and A2: A13 is the range to be added]sum Orange cell: = SumByColor (A3, A2: A13) [as ' A3 ' is the address of the cell Orange and A2: A13 is the range to be added] sum of green cells: = SumByColor (A4, A2: A13) [as ' A2 ' is the address of the cell green and A2: A13 is the range to be added.
So, this was all from me on this thread. Don't forget to download the sample spreadsheet [link] ... and let me know if you experience problems when using these methods.

Credits to the post here


comments powered by Disqus