Excel 2003/2007/2010 IF Function

We have all encountered the IF function in Excel but if you aren’t in the Microsoft Suite every day you forget how to use it. You have come to the right place let me jog your memory

The basic syntax for the IF Function is as follows:

In your cell or in the Fx textbox you and enter something like the following:

=IF ( logical_test, value_if_true, value_if_false )

• The logical_test variable is simply a value or expression that is tested to see if it is true or false.
• The value_if_true variable is the value that is displayed if logical_test is true.
• The value_if_false variable is the value that is displayed if logical_test is false.

For example if we had something like

=IF (A>10,”High”,”Low”)

In this  case if Column A contains a value higher than 10 then whatever cell you have this IF function in would contain the value High, else if it does not it would have “Low.”

Another example would be something like

=IF (A>20000,B=5000,B=2500)

You could use something like this for salaries, if your salary is greater than $20,000 then something like column B is $5,000. Column B could be something like bonus. Your Bonus might be less if your making less than 20K something like $2,500.

I hope this gives  you a few real world examples. If you need some screenshots I can provide some. As always let me know if you have any questions. Next time we might get a little more technical and do some nested IF functions. Stay Tuned.

-Digital Hoarder

Windows Batch Script To Share Folders and Grant Permissions

So I have this unique situation where I need to share particular folders on startup of my Windows Workstation as I have multiple Truecrypt drives that I mount upon login via another batch script that is also an additional tutorial on the blog here at Unknown Tech Solutions. I am going to show you how to use the built in Windows “NET SHARE” command to share out folder/drives to particular windows users. You can do this via the command prompt or place the info in a Windows Batch Script like I am doing.

So I use REM as providing output to the user so inthis case when I click on the batch script I will first get an output stating that:

Creating the Rapture HD Share on Drive W: and grants usernamehere Full Access.

Next we use the command NET SHARE

Then followed by the share name I like to put them in parentheses

NET SHARE “Share Name Here”=”Share Location Here”

If you want to share the whole drive you would just leave it as

NETSHARE”Share Name Here”=Drive Letter Here

For example:
NETSHARE “My Windows Share”=D:/

or

NETSHARE “My Windows Share” =”D:/Shared Files”

Then we use the /GRANT switch to grant a particular user access and which kind of access

For Example

NETSHARE “My Windows Share” =”D:/Shared Files” /GRANT:”Username”,FULL

The aforementioned will grand the user “Username” full access to that particular share

Here is more information on NET SHARE:

http://technet.microsoft.com/en-us/library/bb490712.aspx
Below you will see in the command prompt some of the switches that the article above explained in more detail.

Let me know if you have any other questions.

-Digital Hoarder

Mounting TrueCrypt Volumes From a Windows Batch Script

I took the task of encrypting all of my drives with Truecrypt a while back and it became very apparent that it was going to be a pain to manually mount the drives every time I logged into my Windows workstation. I didn’t want to load them at boot time like some users choose to do as I chose the hidden partition option when initially creating them. I thought if I housed the batch script on a flash drive and that information never got copied but only ran from the flash drive then I could keep the script on the USB thumb drive and insert and run when I was ready to mount the Truecrypt drives.

For more information on command line usage from Truecrypts site:

http://www.truecrypt.org/docs/?s=command-line-usage

I will throw in some examples of what  a typical .bat file would look like in regard to Trucrypt and dissect it piece at a time so you can better understand it.

This is what is contained in my mountruecrypt.bat file housed on my thumbdrive.

REM Mounting HDD1_DATASTORE_1
“c:\program files\truecrypt\truecrypt.exe” /volume “\Device\Harddisk3\Partition2” /letter w /password “passwordinstertedhere” /quit

So REM is a command to display output to the user so in this case REM displays “Mounting HDD1_DATASTORE_1”
to me when I initially click on the script and run it.

REM Mounting HDD1_DATASTORE_1

The next part in quotations is where the truecrypt installation and executable is stored. I have mine stored within the quotations.

“c:\program files\truecrypt\truecrypt.exe”

The next piece is what volume I want to mount. You can find out by clicking on devices within the Truecrypt program. I used the /volume switch or you can just use /v.

My example:

/volume “\Device\Harddisk3\Partition2”

Your example:

/volume “entervolumenamehere” or /v “entervolumenamehere”

Next we have what drive you want to mount it as you can use /letter or /l as the switch so:

My example:

/letter w

Your example:

/l yourdriveletterhere or /letter yourdriveletterhere

Next up is the password which is /password or /p for the switch.

My example where passwordinsteredhere is the password:

/password “passwordinstertedhere”

Your example:

/password “yourpassword” or /p “yourpassword”

NOTE: In batch scripts it doesn’t like special characters because it wants to treat your “” as a special character so I made sure that my particular passwords are 256 bit strength in length but only consist of uppercase and lowercase letters and numbers. I’m sure there is probably a way to do this to account for special characters but for the purpose of this tutorial I am keeping it simple for the average user.  If you do know how to do this please comment so I can learn something 🙂

The last switch is for a silent mode it basically runs the command and then exits to the background i.e. system tray. You don’t have to do this but this way the Truecrypt window will not pop up so in my opinion its more streamlined and clean.

You can use /quit or /q

After you are done writing your script make sure it has a .bat extension as Windows will recognize that as a batch script. Please let me know if you have any questions. For security purposes I always recommend never leaving this script anywhere on your drives/desktop/workstation rather on a separate device as it will contain the password of your Truecrypt drive. As always drop me a comment if you have any questions.

-Digital Hoarder

F5 BIG IP Global Traffic Management and Infoblox IP Address Management DNS Integration

I have been tasked at implementing Global Traffic Management at my place of work as we have used F5 for a number of years in the local traffic management realm. After the infrastructure team recently implemented Infoblox’s IPAM appliances/virtual instances we learned that in a now GA release that GTM and Infoblox were going to be providing a single pane of glass for DNS management. This makes it far easier as we will no longer have to update aliases, records, etc. in two places.

For those of you that are interested here are some of the sites that you can find out more information:

http://www.infoblox.com/en/solutions/load-balancing.html

http://www.infoblox.com/content/dam/infoblox/documents/datasheets/infoblox-datasheet-load-balancer-manager.pdf

http://www.f5.com/it-management/solution-profiles/infoblox-dns-infrastructure-global-traffic-management

Some of the key takeaway features are:

• The ability to easily deploy and manage with combined DNS Services, DHCP, IPAM and Global App Delivery solutions in one user interface as I mentioned above.

• Dramatically scale up to 10x for hyper volumes, reduce DNS latency for fast web browsing, and the ability for DNSSec, increased security.

• Automation of daily DNS infrastructure operational tasks such as status checking and synchronizing updates

• Ease of administration with the ability to assign roles, reduce administrative overhead and maximize value from existing DNS you have today as well as global traffic infrastructure.

Please let me know if you have any other questions

-Digital Hoarder

Adding Custom Thumbnails or Editing Existing Thumbnails in Xbox Media Center / XBMC

When allowing album art covers to be downloaded for your audio/video collection in xbox media center you may notice that some of the covers are incorrect. You may have to manually fix some of them and here’s how:

My tutorial specifically applies to video collection, xbmc applies thumbnails to video files in the following order:

1) Cached Thumbnail Files

Thumbnails are cached to UserData\Thumbnails\Video using the CRC of the path of the file or folder.

2) User-Defined Thumbnail Files

For files: XBMC checks for.tbn and uses that if it exists.The thumbnail is cached the first time it is read.

For movies, movie.tbn will override any other thumb.

For folders: XBMC first checks for.tbn in the same directory as the folder is in. If no.tbn exists XBMC then checks for folder.jpg inside the movie-folder. In either case the first found thumb of those is cached.

Please comment if you have any questions.

Thanks,

Digital Hoarder

Mozilla Firefox Private Browsing

In this day and age it is always important to practice security where you can and this simple change can make your web browsing more secure by browsing anomalously. This only pertains to Mozilla Firefox but it is widely used and similar browsers such as Internet Explorer and Google chrome have alternative options. With your browser open in the upper left hand corner there is a Mozilla “Firefox” button (if you have the most up to date version of Mozilla Firefox otherwise it will be under “Tools” > “Options” ) click on it and you should see an “Options” button click on that and then select the Privacy tab/panel as shown below.

Step 2: As shown below under “History” select Use custom settings for history from the drop down list.

Step 3: In addition, check the box next to “Always use private browsing mode ” as shown below.

Step 4: Click “Ok” to close the window, also it would be a good idea to disable the remembering of passwords this is a terrible practice/habit to get into. With the advancement of viruses, malware, and ad-aware you should never enable the remembering of passwords in your browser. You will notice that from now on when you open up Firefox you will see the Firefox button in a purple color instead of orange this reminds you that you are in “Private Browsing” mode.

If you have any questions please comment. Thanks,

Digital Hoarder

How To Use the Mass Update Feature in Sickbeard

If you are like me and have a Usenet account and use SABNZBD you might have heard of Sickbeard if you haven’t you should familiarize yourself with the program as it saves you loads of time when it comes to a DVR/PVR type Application for more information simply type in “Sickbeard” in Google. I use it to keep track of all of the TV shows I watch and download them for me when available it is also possible to do renaming and load of other features.

I’d like to highlight one of the really nice features called “Mass Update” as this particular feature allows you to manage all of your TV shows simultaneously. For example if you want all of them to be in standard or high definition you can modify all of them at once or perhaps you want them to be put into season folders where the directory structure would look like the following

Example: TV Directory > TV Show Name > TV Show Season > TV Show Files

               D:/TV > The Sopranos > Season 1 > Sopranos 1×01 Episode Title

Under the “Manage” tab, click on “Mass update”

 

Click on “Mass Update” as shown.

Here select the top “Edit” button which will select all of your TV Shows

 

Scroll down to the bottom of the browser page where you will click on “Edit Selected.”

 

Here as shown above you can adjust the aforementioned settings (also shown below) that I talked about in addition to the root directory where you would like your TV shows to be placed. If you have any other questions please comment.

Thanks,

Digital Hoarder

BIG IP F5 Custom HTTP Header Monitors Differences Between 10.x and 11.x

I noticed that some of my F5 http head monitors that I had existing in our 10.2 environment were not cutting over properly / they were failing in our 11.1 HF1 F5 environment. I did a little digging and found that you need to use two CLRF (carriage line return feeds) instead of one. As you can see from the screenshot below if you add an additional /r/n to the first carriage line return feed your monitor will be successful where as just one will fail. Hope this helps.

Add an additional \r\n to the end of the send string as shown below.

As always if you have any questions please comment and I will assist.

Thanks,

Digital Hoarder

Excel IF Statement to have Weighted Averages By Category

I spent a lot of time trying to figure this out through multiple blogs and Googling so I thought I would share the solution and the equation I came up with. As you can see, I was trying to weight a numerical value and give it an importance of low, medium or high. You might be using this for a different reason such as if it’s a 1 give it a color of red or something of that sort you can modify this equation to your liking but if you have any questions please let me know

Here is the equation I used that if column F had a numerical value of 1 it would autofill column H with a description of low, medium or high based off of column F

=SUM(F55*(IF(H55=”High”,3,IF(H55=”Medium”,2,IF(H55=”Low”,1,0)))))

F55 is column F row 55, so on and so forth you get the picture if not leave a comment I’d be happy to assist.

How To Flush DNS On Solaris

It may be sometime required to flush your DNS Cache mostly when trying to troubleshoot a problem or when youve updated some IP address and need to kill some active connections. On the command line of the Solaris server you can flush dns just as you can through the command prompt on a Windows Box (cmd > ipconfig /flushdns)

The NSCD daemon in Solaris and of course in UNIX and most of the Linux distros maintains the system cache including the DNS/Name service cache.

All it needs is to restart the Daemon to flush the DNS cache.

One the command line you can see the name server cache daemon process running by entering the following command

 ps -ef | grep nscd

This will give you back the process id # you will need to kill the daemon in order to restart the process

Now on the command line enter the following

pkill

Example: pkill 9955

Or just:

kill <process id #>

Example: kill 9955

Now you can check if the nscd process is killed or is still running

ps -ef | grep nscd

Now, restart the nscd daemon by entering

/usr/sbin/nscd

This should start the nscd daemon and there you go, you have flushed the DNS Cache in your Solaris Server.

Verify that is is backup and running by:

ps -ef | grep nsd