Excel Formula : Convert a text to Number

Got a Excel file from other resource witch Column A is set as text. At the tail and start of number there are some spaces filled in, for example cell A1 is '  4 '.

My challenge is to convert whole column to numbers which can be used to do sum or other math calculation.

With some research, I constructed this formula for a new column :
=VALUE(TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))))

it perfectly resolved this challenge as you can see from following screen shot.

Note: trim will not work with the only space cell. Thanks reply from Hari Krishna.

Excel Formula: Search a Column of Strings to Match Another Column

Once a while, I have to work on Excel sheet manually. Today I have to search exported hundreds of ip addresses in a pre-defined excel spreadsheet to see if there is a match. It seems a easy work, but it took me almost an hour to find right formula.

Column O includes all exported ip addresses. I have to manually search if those column O's ip addresses are appearing in column B's text. If yes, which row is it?

Formula is set at Column N.

=MATCH("*"&(O6)&"*",B:B,0)

As you can see from cell N6, the number is 7, which means the text in B7 includes O6's string.
N12's number is 5, which means the test in B5 includes O12's string. 

Microsoft Office Tips and Tricks (Word, Excel, Visio, PowerPoint)

Online PDF to Word

• Convert PDF to Word (Free!)
• SmallPDF

1. Word

1.1 Convert Texts into a Table:

 

excel function in accounting

https://www.youtube.com/watch?v=u1ieHLBFxAU

Excel Tips

How to Create a General Ledger Worksheet with Excel

https://www.youtube.com/watch?v=9ByjpqzXRTc

Excel Power Tips: 10 Ways to make Data Entry Faster and More Accurate

https://www.youtube.com/watch?v=6BErKQ29Jjc

Sage Simply Accounting - free download and use for 14 months

Sage's Simply Accounting can be free used as education purpose. Here are some basic steps

1. Download:

Download Student Version     Serial Number
Sage Simply Accounting 2011    242P1U2-1000002 (Bilingual)
Sage Simply Accounting 2010    242P2U2-1000001 (Bilingual)
Sage Simply Accounting 2009    242P1U2-1000001 (Bilingual)
Sage Simply Accounting 2008    242P2U2-1000000 (English Version)
                                                  242P2UF-1000000 (French Version)
Sage Simply Accounting 2007    24272U2-1000001 (English Version)
                                                  24272UF-1000001 (French Version)
Sage Simply Accounting 2006    24272U2-1000000 (English Version)
                                                  24272UF-1000000 (French Version)

2. Registration

After installation, you will be asked to register your product and activate it.

For version 2012 and later, Please open following page to get registration information:

Sage 50 Accounting—Student Version Registration

Register your copy of Sage 50 Premium Accounting 2015—Student Version (formerly Sage Simply Accounting Premium - Student Version). Sage is committed to providing students with the necessary tools to be successful in the classroom—and in the workplace! Please note that if you select the wrong version, you will not be able to activate your software. If you are not sure which version you are using, right click on the logo on your desktop, view the Properties tab, and note the version information.
Product Version:	Version 2015 (Premium) Version 2014 (Premium) Version 2013 (Premium) Version 2012 (Premium)

To register your copy of Sage Simply Accounting

A key code is entered when a new user activates their program or when an existing user upgrades their current version of Sage Simply Accounting.

Step 1: Obtain an Account ID and key code

Contact us by telephone, or use the online form on our Web site to register your product:

• During business hours (Monday through Friday 6:00am – 5:00pm Pacific Time), call 1-866-797-8395.

Following registration, you will receive a registration confirmation e-mail containing your Account ID, Key code, and a password.

• Account ID. This is your ten-digit account identification number.
• Key Code. This is a string of 23 alphanumeric characters and is not case sensitive. The key code does not use the letters O and I. If a letter looks like an O or an I, try entering the key code again using the numbers 1 and 0 instead.
• Password. The password enables you to access users-only areas on our web site.

Step 2: Enter the activation information in your program

If Sage Simply Accounting is currently open on your desktop

1. On the Help menu, select Enter Key Code.
2. Enter the following information:
   • Company Name. Enter your company name as it is shown in your registration confirmation e-mail.
   • Serial Number. This box should be filled in for you. If not, enter the serial number of the Sage Simply Accounting edition that you are trying to activate.
     Your serial number can either be found on the Sage Simply Accounting DVD case or emailed to you if you did not purchase a disc.
   • Account ID. This is your ten-digit account identification number. You can find this number in your registration confirmation e-mail. Do not include any spaces or dashes.
   • Key Code. You will find this in your registration confirmation e-mail. The key code is a string of 23 alphanumeric characters, is not case sensitive, and does not use the letters O and I. If a letter looks like an I or an O, try entering the Key Code again using the numbers one and zero instead .
     • To manually enter your key code, check Use this Key Code, and then enter the key code.
     • To have Sage Simply Accounting get it for you, ensure that you are connected to the Internet, and then check Retrieve my Key Code from Sage Simply Accounting Online. Sage Simply Accounting displays a confirmation message once your key code is processed.

How to Close the Year End in Accrual Basis Accounting

by Isobel Phillips, Demand Media 

The year-end process is the same, whether you use a manual or computerized accounting system.

Related Articles

• Does Accrual Accounting Affect End of Year Accounts?
• Financial Issues When Setting Up Accrual Basis Accounting
• Why Is the Accrual Basis of Accounting Accepted by GAAP?
• What Does "Year-End Accruals" Mean?
• What Are the Advantages and Disadvantages of Accrual Basis Accounting?
• The Basic Steps in the Management Planning Process

Many small businesses use a straightforward cash method of accounting, reporting income in the accounting year in which they receive it and deducting expenses in the year in which they pay them. However, if your business has any kind of inventory, you must use the accrual method, in which you report income and expenses in the year in which you earned or incurred them. At the end of your accounting year, you make adjusting entries to match sales and purchases in the correct year before closing the year end.

Prepare the Adjusting Entries

Step 1

Create a backup of your computerized accounting system and prepare an income statement and balance sheet report.

Step 2

List all expenses paid in advance, where the term of the expense goes into the next accounting year. For example, if you pay annual insurance premiums of $1,000 on July 1 and your accounting period ends on December 31, at year-end, you will have prepaid six months of cover, or $500, for next year.

Related Reading: Financial Issues When Setting Up Accrual Basis Accounting

Step 3

Credit the expense account with the prepaid expense, reducing the expense for the year, and debit the prepayments account in the balance sheet. In the example, credit the insurance account with $500 and debit prepayments with the same amount.

Step 4

List all expenses the business has incurred but not yet recorded or paid. For example, your last electricity bill was for the quarter that ended October 31 and you will not receive another bill until after January 31. Estimate the cost of electricity for November and December.

Step 5

Debit the expense account and credit the accruals account in the balance sheet with the accrued expense. For example, if you estimate your electricity expense for the two months at $750, debit the utilities account and credit accruals with $750.

Step 6

List any income the business has earned but not yet billed. For example, you may invoice customers in January for work done in December. This is accrued revenue and should be included in December's income.

Step 7

Credit the revenue account and debit accounts receivable with the amount earned in December. For example, if you have recorded $600 of work in December to be billed in January, credit the revenue account with $600 and debit accounts receivable in the balance sheet with the same amount.

Step 8

Calculate the depreciation of the company's fixed assets to spread the cost of each asset over its useful life. For example, if you use the straight-line method of depreciation, you depreciate your assets by an equal amount in each accounting period.

Step 9

Debit depreciation expense account with the total depreciation for the year and credit accumulated depreciation in the balance sheet. If you have calculated depreciation on motor vehicles to be $1,000 and depreciation on office equipment at $500, debit depreciation expense account and credit accumulated depreciation with $1,500.

Make the Closing Entries

Step 1

Prepare an adjusted trial balance and income statement.

Step 2

Close all income statement accounts that have a credit balance by debiting them with the amount of the credit balance and posting the same amount as a credit to the temporary income summary account. For example, if you have three income accounts with credit balances of $5,000, $20,000 and $75,000 respectively, debit each account with the amount of its credit balance to close it and credit the income summary account with $100,000.

Step 3

Close the income statement accounts that have debit balances. Credit the account with the amount of its balance to close it and debit the income summary account with the same amount. For example, if your utilities account has a debit balance of $5,000 at year-end, credit utilities and debit income summary account with $5,000.

Edit bills and bill payments

Edit bills

You can edit all bills To change the amount due on a bill,

 Use these steps to change the amount due on a bill.

What's happening behind the scenes?

If you reduce the bill amount, QuickBooks creates a credit with the vendor who you overpaid. If you increase the bill amount, QuickBooks considers the bill not fully paid until you pay the additional amount.

To do this task

1.     Find the bill.

1.     Click the Vendors icon.

2.     If you know the vendor's name, select the vendor on the Vendors tab, then click the Show drop-down list and choose Bills.

Otherwise, click Bills on the Transactions tab to get a list of bills for all vendors.

3.     (Optional) Use the Filter By and Date drop-down lists to narrow down the set of bills displayed.

4.     Double-click the bill to open it.

2.     Make the necessary changes to the bill.

3.     Save the bill.

o    Click Save & Close to save the transaction and close the window.

o    Click Save & New to save the transaction and enter a new one.

Edit bill payments

Use these steps to change the amount you paid on a bill.

To do this task

1.     Find the bill payment.

a.     Choose Vendors > Vendor Center.

b.     If you know the vendor's name, select the vendor on the Vendors tab, then click the Show drop-down list and choose Bill Payments.

Otherwise, click Bill Payments on the Transactions tab to get a list of bill payments for all vendors.

c.     (Optional) Use the Filter By and Date drop-down lists to limit the set of bill payments displayed.

d.     Double-click the bill to open it.

2.     Make the necessary changes to the payment.

3.     Save the payment.

Change a discount you've already applied to a bill payment

You can change the amount of a discount that you have previously applied to a bill payment.
To do this task

1. Go to the Vendors menu and click Pay Bills.
2. Select and highlight the bill that has the discount that you want to change.
3. Click the Set Discount button.
4. Change the amount of the discount.
5. Save the bill payment.                                                                                                                       Change a credit you've already applied to a bill payment                            To do this task
6. 1. Go to the Vendors menu and click Pay Bills. 
   2. Select and highlight the bill that has the credit you want to change.
   3. Click the Set Credits button.
   4. Highlight the credit that you want to change.
   5. Change the amount in the Amt. To Use column or click the Clear button to not use the credit.
      Repeat steps 4 and 5 for each credit that you want to change for this bill.
   6. Click the Done button.                                                                                                                                
      

      Delete a credit from a vendor

      You can delete a credit after you've used it to pay a bill from a vendor.
      To do this task
      

      1. Click the Vendor Center icon at the top of the QuickBooks window.
      2. Click the Transactions tab.
      3. Find the credit you want to delete (it will have Credit in the Type column)
      4. Go to the Edit menu and click Delete Credit 

Vlookup funtion

V-lookup function can find the value in the other range.

1.       Name a rang, what rang you want to look up.

2.       Use v-look up function ,

3.result show up

         4.copy the function to other cells

5. firona is not in list, so can not lookup, you also can do ifError function, to show if it do not in the cell, what show up

5-1 copy  vlookup function to if error function, in the Value if error put  what you want to show up.

5-2 the result

Unrecoverable error 19758 63847 when starts QuickBooks

QuickBooks Pro 2013 met a problem today. It suddenly could not start it correctly. When double clicked the Quickbooks Icon on the desktop, after it loaded the homepage, an security information came up. No matter you choose Yes, No. It just gave you an error information then quit.

The error message windows title is QuickBooks - Unrecoverable Error. At the bottom of this error message window, it shows code : 19758 63847. 

Looked for the solution from Quickbooks support site and found this article:
http://support.quickbooks.intuit.com/support/articles/SLN64823

Another solution is http://dataservices.intuit.com/support/Articles/HOW13974

Quickbooks Componet Tool can be downloaded from this address:

https://intuitcorp.quickbase.com/up/bgg489uvx/g/rh/eh/va/QB_Component_Repair_Tool.exe

After the installation, please restart your machine then Quickbooks should be ok as before.

How to write a cover letter

Here are some important points you have to write into your Cover Letter:

1. Your Contact Information

Name

Address

City, State, Zip Code

Phone Number

Email Address

Date

2. Employer Contact Information (if you have it)

Name

Title

Company

Address

City, State, Zip Code

Salutation
Dear Mr./Ms. Last Name, (leave out if you don't have a contact or using Dear Hiring Manager )

• Dear Hiring Manager
• To Whom It May Concern
• Dear Human Resources Manager
• Dear Sir or Madam
• Dear Company Name Recruiter

Body of Cover Letter
The body of your cover letter lets the employer know what position you are applying for, why the employer should select you for an interview, and how you will follow-up.

3. First Paragraph

The first paragraph of your letter should include information on why you are writing. Mention the position you are applying for and where you found the job listing. Include the name of a mutual contact, if you have one.

4. Middle Paragraph(s)

The next section of your cover letter should describe what you have to offer the employer. Mention specifically how your qualifications match the job you are applying for. Remember, you are interpreting your resume, not repeating it.

5. Final Paragraph

Conclude your cover letter by thanking the employer for considering you for the position. Include information on how you will follow-up.

Dear Mr./Ms. Last Name:

I am interested in the Coordinator position advertised on XYZ. My resume is enclosed for your review. Given my related experience and excellent capabilities I would appreciate your consideration for this job opening. My skills are an ideal match for this position.

Your Requirements:

My Qualifications:

I appreciate your taking the time to review my credentials and experience. Again, thank you for your consideration.

6. Complimentary Close

Respectfully yours,

• Sincerely
• Sincerely yours
• Regards
• Best regards
• Kind regards
• Yours truly
• Most sincerely
• Respectfully
• Respectfully yours
• Thank you
• Thank you for your consideration

Follow the closing with a comma, a space, and then your name and your contact information, if you're sending an email message. For example:

Best regards,

Your Name
Your LinkedIn Profile URL
Your Email Address
Your Phone Number

7. Email Subject Line

When you're sending an email cover letter, include a Subject line that enables the hiring manager to recognize who you are and the job for which you are applying.

Email Subject Line Examples

• Managing Director Position
• Job Code 1234: District Sales Manager - Your Name
• Communications Assistant Position - Your Name
• Application for Customer Service Job
• Social Media Expert Seeking New Opportunity
• Meeting Follow Up - Your Name
• Meeting Request - Your Name
• Referred by Name
• ABC College Informational Interview Request