Thanks to antville.org
Excel formula tip

While you enter a formula in a cell, press CTRL+SHIFT+A to see the arguments in a formula. If you type =RATE, and then press CTRL+SHIFT+A, you can see all the arguments for that function--for example, =RATE(nper,pmt,pv,fv,type,guess).

... Link (0 comments) ... Comment


Excel Tips

Quickly Typing Dates into Cells

Typing a large amount of data into cells can be tiring, especially if it includes a series of dates. Type the day (serial number only) into cell A1 and add the following formula to insert the month and year into cell B1:

=DATE (YEAR(TODAY()), MONTH(TODAY()), A1)

Type a full number in the cell and change it to a date by using the following formula:

=DATEVALUE(LEFT(A1,2)&"/"&MID(A1,3,2)&"/" &RIGHT(A1,2))

For example, type 122203, the result is 12/22/03.


Counting number of unique entries in a specified range

By using SUMPRODUCT along with COUNT to count only the number of unique entries in a specified range. The range can be all in one column or row, or may span multiple (adjoining) columns/rows.

The formula would be written as: =SUMPRODUCT((1/COUNTIF(A1:C5,A1:C5&""))) where A1:C5 is replaced with the range you want to apply the count to.


Retrieving an Item from a List that Meets Multiple Criteria

Problem:
Range A2:C7 contains a list of items and their corresponding colors and prices. We want to find the name of the first Blue item that costs less than $200.

Solution:
Use the INDEX and MATCH functions as shown in the following Array formula:
{=INDEX(A2:A7,MATCH(1,(B2:B7=D2)*(C2:C7 < D3)))}


Color numbers based on the value result in Microsoft Excel

There are two ways to color numbers according to criteria:

* Custom formatting for a number with criteria  
* Conditional formatting  

Using custom formats for numbers with criteria
Your options for coloring a number are not limited to displaying negative numbers red. You can color positive and/or negative numbers in any color you wish (do not get excited - the number of colors is limited to eight, and most are difficult to read). Do this by adding the name of the color to the number format in brackets.

Example: [BLUE] #,##0 ;RED

A positive number is displayed in blue; a negative number is displayed in red; and 0 is displayed in blue (since there is no third section, 0 receives the format of the positive number by default).

Add a condition to the formatting, and have each section be displayed in a different color: [BLUE] [>5000]#,##0 ;RED; #,##0


Indicators for the maximal and minimal values
Problem:
Creating a formula that will indicate the maximum and minimum values in List1 by returning ""Max Value"" or ""Min Value"" in the cell next to them.

Solution:
Enter the following IF statement, using the MAX and MIN functions, in column B: =IF(A2=MAX($A$2:$A$8),""Max Value"",IF(A2=MIN($A$2:$A$8),""Min Value"",""""))

... Link (0 comments) ... Comment


Difference in Months

=DATEDIF(C3,NOW(),"M")

The DATEDIF function calculates the difference between a starting date and an ending date. The "M" used in the formula indicates that you want the result in completed months.

... Link (0 comments) ... Comment


Saving Common Formulas in Excel

You will have to assign names to your formulas.

  1. Enter your formula as you normally would.
  2. Copy the formula. (Not the entire cell)
  3. Choose Name from the Insert menu, then choose Define.
  4. In the Names in Workbook box, enter the name you want assigned to this formula.
  5. Paste the formulae in the Refers To box.
  6. Make sure there are no dollar signs in the formula. If there are, select them and delete them. (This method of using formulas does not work well with absolute references.)

Now, whenever you want to use the formula, you simply enter an equal sign and the name you gave to the formula.

... Link (0 comments) ... Comment


Excel formulas

To apply data validation to a column which allows only unique items to be entered, highlight that column and select Data, Validation from the menu. Choose the custom option and enter the following formula (for column A):

=COUNTIF($A$1:A1,A1)=1

This array formula returns the number of unique items in a worksheet range.

=SUM(1/COUNTIF(Rng,Rng))

... Link (0 comments) ... Comment


Outlook Tip

You can add signature to all outgoing mails.

Tools - Options - Mail Format - Signatres...
Add you own signature line for e.g. name, email and contact number.

... Link (0 comments) ... Comment


E-mail tips

Spell Check
Here’s what you need to do to get Outlook's spell checker to kick in for every single mail that you send out.
Select Tools > Options and switch to the Spelling tab. Here, under Settings, tick the check box next to “Always check spelling before sending” and then click on OK.
Now the next time you your type out a mail and hit the Send button, the spell checker will point out any and every typo you have made.

Address Book Addendum
Double-click on an e-mail you have received from someone to open it. Right click on the sender's mail address (the From field). Next, select Add to Address Book.
To edit the person’s name you have just added, choose File > Address Book. Now select the name and click on File > Properties. Edit the name as per requirements and click on OK.

Bunching Fowards
If you want to forward several e-mail messages at the same time to someone, go to the folder containing the messages you'd wish to forward. Holding the Ctrl key down, click on each message you want to forward. Then, right-click on any message in your selection and pick Forward. A New Message window will appear with the messages attached (across the bottom of the window). Now simply complete your message and zap it off.

... Link (0 comments) ... Comment


Numbers in Thousands

Is there a number format that I can use that will display numbers in the thousands without actually using a formula? So, 20,000,000 in sales in a cell would actually be displayed as 20,000.

Select cell where you have typed 20,000,000
Choose Format - Cells and type the form #,##0, in the custom field. The number format #,##0, works because of the comma at the end.

... Link (0 comments) ... Comment


Sent to me?

You can set Microsoft Outlook to color-code all email addressed only to you blue, because those messages are more likely to be more important and require action from you.
To get this set up in Outlook, from the Tools menu choose Organize, and then in the "Using Colors" section hit the "Turn on" button next to "Show messages sent only to me in Blue."

... Link (0 comments) ... Comment


Hiding cells

Use a custom format for the cells whose content you want to hide. Follow these steps:

  1. Select the cell (or cells) you want to hide.
  2. Choose the Cells option from the Format menu. Excel displays the Format Cells dialog box.
  3. Make sure the Number tab is displayed. (Click here to see a related figure.)
  4. In the list of format categories, select Custom.
  5. In the Type box, enter three semicolons (;;;).

Now the information in the cell is not visible, nor will it print.

... Link (0 comments) ... Comment


Online for 6561 days
Last modified: 4/12/20 2:01 PM
Status
Youre not logged in ... Login
Menu
... Home
... Tags

Search
Calendar
August 2020
SunMonTueWedThuFriSat
1
2345678
9101112131415
16171819202122
23242526272829
3031
April
Recent updates
important leafy vegetables 1. Amaranthus
caudatus: rajgira. 2. Amaranthus tricolor: Math.  (Chhoti Chulai- Hindi, Cheera-...
by shantanuo (4/12/20 2:01 PM)
program creek program creek
seems to be a good site for handy references of python...
by shantanuo (4/11/20 11:24 AM)
Standard out to a file
This code will change the standard out to a file...
by shantanuo (4/11/20 11:18 AM)
Create config file for AWS
command line tool While creating amazon config file in a...
by shantanuo (2/20/20 6:00 AM)
setting colors in pandas This
is how to set the colors in pandas mapper =...
by shantanuo (2/20/20 5:53 AM)
log mysql output tee output.txt
show global variables; show engine innodb status\G show full processlist;...
by shantanuo (2/19/20 10:33 AM)
access AWS API gateway using
python Amazon API gateway allows us to host server-less API's...
by shantanuo (2/16/20 9:41 AM)
Using boto In the bad
old days, the glacier class used to have different layers....
by shantanuo (2/15/20 10:28 AM)
backup mysql users  3
steps to install perconal utility... yum install perl-DBI perl-Data-Dumper perl-DBD-MySQL wget...
by shantanuo (2/13/20 1:49 PM)
Steps to be used for
generating AWS API gateway a) Method Execution - Get Method...
by shantanuo (2/13/20 1:30 PM)
apply colors to pandas Here
is how to change the color of negative values to...
by shantanuo (2/12/20 8:57 AM)
Holidays in India These are
the common holidays celebrated in India (Mumbai) during 2016 -...
by shantanuo (2/11/20 1:53 PM)
change log file size in
MySQL Here are the steps to be followed while changing...
by shantanuo (2/11/20 10:38 AM)
MySQL common error messages 1146
Table does not exist 1054 column does not exist 1062...
by shantanuo (2/11/20 10:13 AM)
ipython parallel demo First start
ipython in cluster mode ipcluster start -n 4 from IPython.parallel...
by shantanuo (2/9/20 9:02 AM)
djanog settings You may see
this message if you have DEBUG = True in your...
by shantanuo (2/9/20 4:07 AM)
clear tmp folder Here is
following commands to clear unwanted stuff from the /tmp partition...
by shantanuo (2/8/20 2:41 PM)
change your server time to
IST If you need to change your server clock to...
by shantanuo (2/8/20 2:39 PM)
Transfer root emails to another
email address If you have root access to the server...
by shantanuo (2/8/20 2:32 PM)
special strings in cron Instead
of the first five fields, you can use any one...
by shantanuo (2/8/20 2:30 PM)
LibreOffice version conflict I am
not able to install latest version of Libre office. I...
by shantanuo (1/29/20 9:15 AM)
while loop This is
a sample shell script while loop while read mydb do echo...
by shantanuo (1/26/20 12:24 PM)
compare mysql schema And here
is poor man's schema compare using good old diff command......
by shantanuo (1/26/20 11:49 AM)
Selecting from error log grep
"error" error_log |awk -F[ {'print $4'} \ | sort |...
by shantanuo (1/26/20 10:40 AM)
Rank mysql records using variables
SET @rank = 0, @prev_val = NULL; SELECT rank, correct...
by shantanuo (1/26/20 9:45 AM)

RSS feed

Made with Antville
Helma Object Publisher