Question about Microsoft Excel for PC

1 Answer

How do I say look at cell on left rather than the cell number

I have used a vlook up formula (which works) however when the result is found I need the code to look to the cell on left and if the cell contains a particular word I need the value placed, if not I would like it to be 0.

Posted by on

  • carole134 Nov 18, 2008

    Thank you for your assistance, I had already fixed the issues using the same advice you have given above.



    Regards



    C.

×

1 Answer

  • Level 1:

    An expert who has achieved level 1.

    Hot-Shot:

    An expert who has answered 20 questions.

    Corporal:

    An expert that hasĀ over 10 points.

    Mayor:

    An expert whose answer gotĀ voted for 2 times.

  • Contributor
  • 45 Answers

Vlookup can only locate values within the lookup range and to the right of the lookup column. to address your concern, i'd advise you to create a dummy column within the lookup range (and hide it) and link it to the lookup column. this way, we won't need a (-) column index number.

Posted on Nov 17, 2008

1 Suggested Answer

6ya6ya
  • 2 Answers

SOURCE: I have freestanding Series 8 dishwasher. Lately during the filling cycle water hammer is occurring. How can this be resolved

Hi,
a 6ya expert can help you resolve that issue over the phone in a minute or two.
best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.
the service is completely free and covers almost anything you can think of (from cars to computers, handyman, and even drones).
click here to download the app (for users in the US for now) and get all the help you need.
goodluck!

Posted on Jan 02, 2017

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

2 Answers

I NEED MS EXCEL CONVERT FORMULA FOR NUMBER TO CURRENCY


Here is a very popular bit of code from Microsoft that will convert any currency amount in a cell to English words. All code and text from below here is the work of Microsoft.

Summary
This article shows you how to create a sample, user-defined function named ConvertCurrencyToEnglish() to convert a numeric value to an English word representation. For example, the function will return the following words for the number 1234.56: One Thousand Two Hundred Thirty Four Dollars And Fifty Six Cents

The Function Wizard can also be used to enter a custom function in a worksheet. To use the Function Wizard, follow these steps:

1. Click the Function Wizard button, and select User Defined under Function Category.
2. Select ConvertCurrencyToEnglish, and enter your number or cell reference.
3. Click Finish

To Create the Sample Functions

1. Insert a module sheet into a workbook. To do this in Microsoft Excel 97 or Microsoft Excel 98, point to Macro on the Tools menu, and then click Visual Basic Editor. In the Visual Basic Editor, click Module on the Insert menu. In Microsoft Excel 5.0 or 7.0, point to Macro on the Insert menu and click Module.

2. Type the following code into the module sheet.
Function ConvertCurrencyToEnglish (ByVal MyNumber)

Dim Temp

Dim Dollars, Cents

Dim DecimalPlace, Count



ReDim Place(9) As String

Place(2) = " Thousand "

Place(3) = " Million "

Place(4) = " Billion "

Place(5) = " Trillion "



' Convert MyNumber to a string, trimming extra spaces.

MyNumber = Trim(Str(MyNumber))



' Find decimal place.

DecimalPlace = InStr(MyNumber, ".")



' If we find decimal place...

If DecimalPlace > 0 Then

' Convert cents

Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)

Cents = ConvertTens(Temp)



' Strip off cents from remainder to convert.

MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))

End If



Count = 1

Do While MyNumber <> ""

' Convert last 3 digits of MyNumber to English dollars.

Temp = ConvertHundreds(Right(MyNumber, 3))

If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars

If Len(MyNumber) > 3 Then

' Remove last 3 converted digits from MyNumber.

MyNumber = Left(MyNumber, Len(MyNumber) - 3)

Else

MyNumber = ""

End If

Count = Count + 1

Loop



' Clean up dollars.

Select Case Dollars

Case ""

Dollars = "No Dollars"

Case "One"

Dollars = "One Dollar"

Case Else

Dollars = Dollars & " Dollars"

End Select



' Clean up cents.

Select Case Cents

Case ""

Cents = " And No Cents"

Case "One"

Cents = " And One Cent"

Case Else

Cents = " And " & Cents & " Cents"

End Select



ConvertCurrencyToEnglish = Dollars & Cents

End Function







Private Function ConvertHundreds (ByVal MyNumber)

Dim Result As String



' Exit if there is nothing to convert.

If Val(MyNumber) = 0 Then Exit Function



' Append leading zeros to number.

MyNumber = Right("000" & MyNumber, 3)



' Do we have a hundreds place digit to convert?

If Left(MyNumber, 1) <> "0" Then

Result = ConvertDigit(Left(MyNumber, 1)) & " Hundred "

End If



' Do we have a tens place digit to convert?

If Mid(MyNumber, 2, 1) <> "0" Then

Result = Result & ConvertTens(Mid(MyNumber, 2))

Else

' If not, then convert the ones place digit.

Result = Result & ConvertDigit(Mid(MyNumber, 3))

End If



ConvertHundreds = Trim(Result)

End Function







Private Function ConvertTens (ByVal MyTens)

Dim Result As String



' Is value between 10 and 19?

If Val(Left(MyTens, 1)) = 1 Then

Select Case Val(MyTens)

Case 10: Result = "Ten"

Case 11: Result = "Eleven"

Case 12: Result = "Twelve"

Case 13: Result = "Thirteen"

Case 14: Result = "Fourteen"

Case 15: Result = "Fifteen"

Case 16: Result = "Sixteen"

Case 17: Result = "Seventeen"

Case 18: Result = "Eighteen"

Case 19: Result = "Nineteen"

Case Else

End Select

Else

' .. otherwise it's between 20 and 99.

Select Case Val(Left(MyTens, 1))

Case 2: Result = "Twenty "

Case 3: Result = "Thirty "

Case 4: Result = "Forty "

Case 5: Result = "Fifty "

Case 6: Result = "Sixty "

Case 7: Result = "Seventy "

Case 8: Result = "Eighty "

Case 9: Result = "Ninety "

Case Else

End Select



' Convert ones place digit.

Result = Result & ConvertDigit(Right(MyTens, 1))

End If



ConvertTens = Result

End Function







Private Function ConvertDigit (ByVal MyDigit)

Select Case Val(MyDigit)

Case 1: ConvertDigit = "One"

Case 2: ConvertDigit = "Two"

Case 3: ConvertDigit = "Three"

Case 4: ConvertDigit = "Four"

Case 5: ConvertDigit = "Five"

Case 6: ConvertDigit = "Six"

Case 7: ConvertDigit = "Seven"

Case 8: ConvertDigit = "Eight"

Case 9: ConvertDigit = "Nine"

Case Else: ConvertDigit = ""

End Select

End Function

Apr 15, 2010 | Broderbund Learn MS Windows XP and Excel...

Tip

HOW TO COUNT THE OCCURRENCES OF A TEXT STRING


In the cell that you want the result to appear in, enter the appropriate formula from the following examples.
How to Count the Occurrences of a Number
Use this formula
=SUM(IF(range=number,1,0))
where range is the range that you want to search, and number is the number that you want to count.
NOTE: This formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.
How to Count the Occurrences of a Text String
Method 1
Use this formula
=SUM(IF(range="text",1,0))
where range is the range that you want to search, and text is the text that you want to find (the text must be enclosed in quotation marks).
NOTE: The above formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.
Method 2
Use the COUNTIF() function to count the occurrences of a text string. For example, use the formula
=COUNTIF(range,"text")
where range is the range of cells that you are evaluating, and text is the text string that you want to count instances of (note that text must be enclosed in quotation marks).
NOTE: This formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.
Wildcard characters can be used within the COUNTIF function.
The asterisk character (*) represents more than one character. For example, to count all the cells in the range a1:a10 that contain an "x," you can use the following formula:
=COUNTIF(a1:a10,"*x*")
The question mark character (?) can also be used to represent one wildcard character -- for example, to count all cells in the range whose second character is the letter, such as "ax" or "bx."
=COUNTIF(a1:a10,"?x*")

on Nov 11, 2013 | Microsoft Excel Computers & Internet

1 Answer

Why don't formulas copy from cell to cell?


Step 1
Determine what formula is to be copied and to what location it is to be copied. Carefully consider the cells that are to be referenced in the formula and that they are indeed the cells that contain the information to be acted upon by this formula.
Step 2
Highlight a cell that has the formula in it and click the right mouse button. Select the "Copy" option.
Step 3
Move to the new destination cell for this formula, right click the mouse button and select "Paste."
Step 4
Check the first cell to make sure the correct calculation has been performed. If not, check the formula that appears in the cell that contains the result of the formula that was just copied. Make sure that the formula references the correct cells. If not, correct the formula and then copy the corrected formula to the rest of the cells that should have this formula applied to them.

The only time I have seen this not work is when the cells/pages are protected.

Note: 
Some formulas will require references to multiple pages or a workbook. Make sure that the absolute cell address is used for specific numbers and those that are to be applied to subsequent cell addresses are clearly denoted by the use of "$" in the cell address.

Dec 17, 2009 | Microsoft Office Home and Student 2007...

2 Answers

How to number a column. I can't remember the short formula, something like a1+1= which would make the number two pop up in the cell underneath.


You can number rows in a column by entering a number in cell A1 (usually the number 1 but youcan start with any number) and the formula (=A1+1) in the next row. The result there will be 2. Copy that formula down the rows you want to number and they will be numbered 3, 4, 5, etc. Each row adds 1 to the previous row so if you do anything that disrupts the sequence (like inserting a row between two others) you will have to copy the formulas down again to restore the sequence. You can also use the Edit-Fill-... menu command to put a series of numbers into rows. Put the starting number in th efirst row. Highlight it and the rows that you want to number and select Edit-Fill-Series... Those numbers will not change if you insert columns or move the formulas.
Or you can use the formula =ROW(A1) in any cell to return the number of that row. (The result of =ROW(A1) is the number 1 in cell A1, the result of =ROW(A2) is the number 2 in cell B2, etc. In this case inserting rows will not affect the numbering (i.e. row A5 will always be numbered 5 even if the data in it is moved down.)

Sep 18, 2009 | Microsoft Excel for PC

1 Answer

I am not able to get all the formulas that are to be used in Companies..How can we use?...I want to get about the procedure for Vlook also?


Most companies do up their own formulas, using a spreadsheet program. After they get the results they are looking for, they either remove any guidelines or copy & paste the parts they want into a word processor document.

Jan 13, 2009 | Computers & Internet

1 Answer

FORMULA NEEDED FAST


Yes you can create the formula outside the cell or you can create it one time and copy it down to the other cells. That way it will populate once it is used.

The formula should be Base Rate * .05 to get the result then add the result back to the base rate.

Dec 18, 2008 | Microsoft Excel for PC

1 Answer

Creating a formula


go to the field with the number of day formula and modify the formula just add to it this *8. The result will be in hrs.
Assuming a holiday uses 8 hrs of pay. It sounds like everyone starts with 25 holiday X 8 hrs or 200 total hours,

Jun 18, 2008 | Computers & Internet

1 Answer

Excel Formula


Make sure that the cell is formatted as a number, not as text. (Right-click the cell, format cell, number, choose NUMBER.)

Dec 19, 2007 | Microsoft Excel for PC

1 Answer

Getting the excel formula


Suppose the value for $ is stored in cell A3. Your formula would look like this: =(A3+A3*0.25)*1.5

The equals sign at the beginning of the formula is necessary. And if you want the result to be formatted as currency, you can do so by right-clicking the cell or column, format cell, number tab, choose currency.

Nov 15, 2007 | Computers & Internet

1 Answer

Forced cell entry


Select the cell(s), then click Data -> Validation. On the settings Tab, in Allow box choose List. In the Source box enter 0, 5. This will only allow these 2 numbers to be entered into the selected cell(s). There are many other validation combinations you can explore, this is the quickest way I found. Hope this helps.

Sep 11, 2007 | Microsoft Office Standard for PC

Not finding what you are looking for?
Microsoft Excel for PC Logo

80 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Computers & Internet Experts

micky dee

Level 3 Expert

2644 Answers

Les Dickinson
Les Dickinson

Level 3 Expert

18381 Answers

Brian Sullivan
Brian Sullivan

Level 3 Expert

27725 Answers

Are you a Microsoft Computer and Internet Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...