Question about Microsoft Excel for PC

Eg I entered the following using the insert function device (Fx beside the line)

=FIXED(Value(Left(N2,2))+Value(Right(N2,2)),0,True)

where N2 refers to a string that contains numbers only ("010203")

Instead of returning the Text "4" I only get

=FIXED(Value(Left(N2,2))+Value(Right(N2,2)),0,True) and no calculation.

I can't figure out why this is happening.

Re: When I enter a formula in a cell with the the insert...

The problem may be that the cell (row or column) has been formatted as Text.

Change the format of the cell to General (or another numeric supported format), then rekey / edit the formula.

Posted on Dec 04, 2007

Re: When I enter a formula in a cell with the the insert...

Is there by any chance a space or any other character before the equals sign? That could cause your formula to display as text.

Posted on Dec 04, 2007

Once you have copy the cell from where the formula cam from, on the destination cell,

go to

1. Edit

2. Paste Special

3. Formula.

4. Press Enter

This should resolve your problem. Regards.

go to

1. Edit

2. Paste Special

3. Formula.

4. Press Enter

This should resolve your problem. Regards.

Aug 11, 2010 | Microsoft Office Excel 2003 for PC

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*")

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 Business & Productivity...

Here's what you are looking for. Browse it and you can find it there all you want for shortcut keys. Follow this link below:

Microsoft Excel Shortcut keys

__Enter data by using shortcut keys____To____Press__

Complete a cell entryENTERCancel a cell entryESC**Repeat the last action****F4 or CTRL+Y****Start a new line in the same cell****ALT+ENTER**Delete the character to the left of the insertion point, or delete the selectionBACKSPACEDelete the character to the right of the insertion point, or delete the selectionDELETEDelete text to the end of the lineCTRL+DELETEMove one character up, down, left, or rightArrow keysMove to the beginning of the lineHOME**Edit a cell comment****SHIFT+F2****Create names from row and column labels****CTRL+SHIFT+F3****Fill down****CTRL+D****Fill to the right****CTRL+R****Fill the selected cell range with the current entry****CTRL+ENTER****Complete a cell entry and move down in the selection****ENTER****Complete a cell entry and move up in the selection****SHIFT+ENTER****Complete a cell entry and move to the right in the selection****TAB****Complete a cell entry and move to the left in the selection****SHIFT+TAB****Work in cells or the formula bar by using shortcut keys****To****Press****Start a formula****= (EQUAL SIGN)****Cancel an entry in the cell or formula bar****ESC****Edit the active cell****F2****Edit the active cell and then clear it, or delete the preceding character in the active cell as you edit the cell contents****BACKSPACE****Paste a name into a formula****F3****Define a name****CTRL+F3****Calculate all sheets in all open workbooks****F9 ****Calculate the active worksheet****SHIFT+F9****Insert the AutoSum formula****ALT+= (EQUAL SIGN)****Enter the date****CTRL+; (SEMICOLON)****Enter the time****CTRL+SHIFT+: (COLON)****Insert a hyperlink****CTRL+K****Complete a cell entry****ENTER****Copy the value from the cell above the active cell into the cell or the formula bar****CTRL+SHIFT+" (QUOTATION MARK)****Alternate between displaying cell values and displaying cell formulas****CTRL+` (SINGLE LEFT QUOTATION MARK)****Copy a formula from the cell above the active cell into the cell or the formula bar****CTRL+' (APOSTROPHE)****Enter a formula as an array formula****CTRL+SHIFT+ENTER****Display the Formula Palette after you type a valid function name in a formula****CTRL+A****Insert the argument names and parentheses for a function, after you type a valid function name in a formula****CTRL+SHIFT+A****Display the AutoComplete list****ALT+DOWN ARROW**

Microsoft Excel Shortcut keys

Complete a cell entryENTERCancel a cell entryESC

on Feb 11, 2011 | Business & Productivity Software

Make sure, when you entr a formula that you hit the ENTER key instead of just moving to tyour next cell. To have all numbers added or moved, use the"Special" application, or change the value. Such as -if you are working in text mode and you enter numbers, they are only numbers in text mode. They don't have a value. Correct the cells to reflect numbers. Right click, format cell - select what you want.

Nov 05, 2009 | Microsoft Office Excel 2003 for PC

You can change this under the Excel, options menu. Select the advanced option and uncheck the option to "Show Formulas in cells instead of their calculated value", or you can jjst press Ctrl + `

This will either turn on or off the show formula option.

This will either turn on or off the show formula option.

Apr 27, 2009 | Microsoft Excel for PC

instead of validation use conditional formating (under format menu). If c1<1 format cell as red. It does not have the attention getter that validation does not require the active cell event. Every other option requires the cell become active for a test.

Apr 13, 2008 | Business & Productivity Software

Just enter using this style

**TIME IN**
**TIME OUT**
** HOURS**
10:00
16:50
6:50
10:22
17:34
7:12
9:00
10:00
1:00

the formula in HOURS is Time Out - Time In

the formula in HOURS is Time Out - Time In

Mar 20, 2008 | Business & Productivity Software

You can refer to cells that are on other worksheets by perpending the name of the worksheet followed by an exclamation point (**!**)
to the cell reference. In the following example, the AVERAGE worksheet
function calculates the average value for the range C1:C10 on the
worksheet named Marketing in the same workbook.

Refers to the worksheet named Marketing Refers to the range of cells between C1 and C10, inclusively

Refers to the worksheet named Marketing Refers to the range of cells between C1 and C10, inclusively

- Click the cell in which you want to enter the formula.
- In the formula bar (formula
bar: A bar at the top of the Excel window that you use to enter or edit
values or formulas in cells or charts. Displays the constant value or
formula stored in the active cell.)
, type
**=**(equal sign). - Click the tab for the worksheet to be referenced.
- Select the cell or range of cells to be referenced.

Jan 01, 2008 | Microsoft Office Standard for PC

Dear Madiha35,

I would recommend the use of the Table Function in Excel.

Here are the steps in Excel 2007, if this does not work for your version please add comment for me to reply to.

Conversely, If you would like the softcopy of the screenshots, I would be happy to email them to you.

Step 1: Enter your data into the worksheet.

Step 2: Create Table

Highlight the relevant data

On the insert tab, click on Table

Step 3: Verify Table range is correct, Click OK

Step 4: Select the cell you where you wish to Sum Data.

Click on Autosum.

Step 5: To Insert new data

Click on the sum row in the table, (Not the entire worksheet row)

Right click, Insert, Insert Table rows from above

Step 6: Enter new data in row

Step 7: Data is automatically calculated in formula.

Oct 22, 2007 | Business & Productivity Software

If I understand correctly, you want to figure the normal wages at 40 hours and less. If there is more then 40 hours, calculate the normal 40 hours, then calculate the hours overtime (time and a half) and add them to get a total.

B5 columns are filled with this:

=IF(A5>$B$1,($B$2*$B$1)+((A5-$B$1)*($B$3)),A5*$B$2)

NOTE: The $ sign doesn't refer to money, it refers to an absolute reference so when copying a formula, the cell references with a $ sign are fixed and don't adjust according to the relative position to where it's being pasted.

Please rate as FixYa! or ask please clarify. ThankYa!

B5 columns are filled with this:

=IF(A5>$B$1,($B$2*$B$1)+((A5-$B$1)*($B$3)),A5*$B$2)

NOTE: The $ sign doesn't refer to money, it refers to an absolute reference so when copying a formula, the cell references with a $ sign are fixed and don't adjust according to the relative position to where it's being pasted.

Please rate as FixYa! or ask please clarify. ThankYa!

Oct 22, 2007 | Microsoft Excel 2003 (06503995)

Jan 28, 2016 | Microsoft Excel for PC

114 people viewed this question

Usually answered in minutes!

I didn't realise that you could not enter formulas into a cell formatted as text.

CNEWTON2 Many thanks

×