Question about Microsoft Excel 2003 (065-03992) for PC

I wish to know how to use nos. from a cell having text also along with the nos. Suppose I have 'episodes 208' in one formatted cell with merged and text wrapped cell, I wish to use figure 208 from this cell to create a formula in another cell, e.g. I wish to divide 100000 by 208 in another cell. Please tell me how to do it if there is a way. Thank you....

Ad

It is possible to do this using the right function.

I created your cells into a worksheet and it is very simple, although I have assumed that the last 3 characters in your cell will be the didigts you need for your formulae.

=RIGHT(B4,3)/1000000

Where the initial text contains more than 3 digits you can adjust for this in the in the formulae with everying else remaining the same.

Posted on May 22, 2009

Ad

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

Ad

#VALUE? is an error indication meaning that Excel can't process the formula. It is most likely the result of a bad cell reference in your formula, for example, you may be trying to perform mathematical operations on a cell that contains non-numeric text data.

Jul 21, 2009 | Microsoft Office Excel 2007

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 Computers & Internet

Auto sum shortcut key

Quickly create a Microsoft Excel formula to get the sum of all cells by highlighting the cells you wish to know the value of and pressing ALT and = .

Create hyper links in Excel

Quickly create hyper links in your Microsoft Word, Excel, or Front Page document by highlighting text and pressing "CTRL + K".

Close all open files at once

Close all open Microsoft Word and/or Excel files by holding down the shift key and clicking "File" and then "Close All".

Quickly open recently opened files

Quickly open the recent files by first pressing "ALT + F" to open the file Menu and press 1, 2, 3, or 4.

Create underlines without any text

This tip works with the majority of Microsoft Office applications. Easily create an underlined space by pressing CTRL + U to start the underline, then press SHIFT + Space for each underlined space you wish to create. An example of how this could be used is for printable forms, for example: Name:__ __.

Print only sections of a document

Save on your printer ink by selectively printing in Windows programs such as Microsoft Word, Internet Explorer, WordPad, Outlook, etc. To do this highlight portions of text you wish to print and click print. In the printer dialog window under Page Range choose the option Selection. It is important to realize that not all programs or printer drivers support this feature.

Repeat recent action

Using the F4 key in Microsoft Office 2000 and above allows the user to repeat the last used action. For example, if you have changed the font of a selected text and wish to change another portion of text to the same font, pressing F4 will do this for you automatically.

Quickly undo

Quickly undo mistakes in the majority of most PC Windows program by pressing CTRL + Z.

Quickly above cell contents

In Microsoft Excel press CTRL + SHIFT + " to copy the above cells contents.

Using the Microsoft Excel Auto filter

Organize cells quickly by using Microsoft Auto filter. Columns that contains long listing of data can be easily sorted by using the Auto filter option, to use this option select the top rows of the columns you want to be used and select Data / Filter and Auto Filter. Once successfully completed you can use drop down arrows to filter the data automatically.

Quickly create a Microsoft Excel formula to get the sum of all cells by highlighting the cells you wish to know the value of and pressing ALT and = .

Create hyper links in Excel

Quickly create hyper links in your Microsoft Word, Excel, or Front Page document by highlighting text and pressing "CTRL + K".

Close all open files at once

Close all open Microsoft Word and/or Excel files by holding down the shift key and clicking "File" and then "Close All".

Quickly open recently opened files

Quickly open the recent files by first pressing "ALT + F" to open the file Menu and press 1, 2, 3, or 4.

Create underlines without any text

This tip works with the majority of Microsoft Office applications. Easily create an underlined space by pressing CTRL + U to start the underline, then press SHIFT + Space for each underlined space you wish to create. An example of how this could be used is for printable forms, for example: Name:

Print only sections of a document

Save on your printer ink by selectively printing in Windows programs such as Microsoft Word, Internet Explorer, WordPad, Outlook, etc. To do this highlight portions of text you wish to print and click print. In the printer dialog window under Page Range choose the option Selection. It is important to realize that not all programs or printer drivers support this feature.

Repeat recent action

Using the F4 key in Microsoft Office 2000 and above allows the user to repeat the last used action. For example, if you have changed the font of a selected text and wish to change another portion of text to the same font, pressing F4 will do this for you automatically.

Quickly undo

Quickly undo mistakes in the majority of most PC Windows program by pressing CTRL + Z.

Quickly above cell contents

In Microsoft Excel press CTRL + SHIFT + " to copy the above cells contents.

Using the Microsoft Excel Auto filter

Organize cells quickly by using Microsoft Auto filter. Columns that contains long listing of data can be easily sorted by using the Auto filter option, to use this option select the top rows of the columns you want to be used and select Data / Filter and Auto Filter. Once successfully completed you can use drop down arrows to filter the data automatically.

on Dec 27, 2009 | Computers & Internet

Brenda,

Your problem is not so much the formula but the cells that contain the hours for each call. Since they contain the text “hrs” (for example “2.25hrs”) this turns the whole cell to text, not numeric, which will always add up to zero in Excel. So do two things:

- Change the cells in column H (Time Spent) to numeric (2.25 instead of 2.25hrs) – you might want to change the column header to Time Spent In Hrs just to b clear

- Use this formula =SUMIF(E8:E26, 1, H8:H26)

For the formula change the middle number to correspond to the operator ID – for example for operator 2 change the formula to =SUMIF(E8:E26, 2, H8:H26)

This should do it.

BTW, sent you a similar email about this.

Your problem is not so much the formula but the cells that contain the hours for each call. Since they contain the text “hrs” (for example “2.25hrs”) this turns the whole cell to text, not numeric, which will always add up to zero in Excel. So do two things:

- Change the cells in column H (Time Spent) to numeric (2.25 instead of 2.25hrs) – you might want to change the column header to Time Spent In Hrs just to b clear

- Use this formula =SUMIF(E8:E26, 1, H8:H26)

For the formula change the middle number to correspond to the operator ID – for example for operator 2 change the formula to =SUMIF(E8:E26, 2, H8:H26)

This should do it.

BTW, sent you a similar email about this.

May 07, 2009 | Microsoft Excel 2007 Home and Student...

Assumption:

**sheet2** cell **a1** like this:

**=IF(Sheet1!e1<>"",Sheet1!b1,"")**

The formula will check**sheet1 **cell **e1**, if there is any number or text put in there, the value of **Sheet1 **cell **b1 **will be copied to **sheet2 **cell **a1**. If you don't supply any number or text in **sheet1** cell **e1**, there's nothing will appeared in **sheet2 **cell **a1**.

If you want some text will display when there's no value in**sheet1** cell **e1 **just change the formula like this:

**=IF(Sheet1!e1<>"",Sheet1!b1,"NONE")**

You can modify it as your like. Just copy the formula to another row in**sheet2 **column **a**.

P.S.: If this information was helpful, please rate this solution.

- we work on
**sheet1**, cell**b1**as description of material and cell**e1**as quantity figure. - we will put the transfer the text in cell
**b1**to**sheet2**cell**a1**if you have entered some value in**sheet1**cell**e1.**Otherwise**sheet2**cell**a1**will left empty or display some text as**"NONE"**or as you wish.

The formula will check

If you want some text will display when there's no value in

You can modify it as your like. Just copy the formula to another row in

P.S.: If this information was helpful, please rate this solution.

Mar 12, 2009 | Microsoft Windows Vista Home Basic for PC

No tmaking promises but try pushing F9 and for future make sure the Automatic caluculation is turned on.

Nov 26, 2008 | Microsoft Excel for PC

formula for the keys or short curt for excel

Jul 15, 2008 | Microsoft Excel for PC

Try using the SUMIF function for one condition or multiple conditions. Use the SUMIF worksheet function to create a total value for one range based on a value in another range. For example, for every cell in the rage B5:B25 that contains the value "Northwind", the following formula calculates the total for the corresponding cells in the range F5:F25. Formula:

=sumif(B5:B25,"Northwind",F5:F25)

Here are SUMIF functions for multiple conditions.

=sum(if((B5:B25="Northwind")*(C5:C25="Western"),F5:F25))

=sum(if((B5:B25="Northwind")+(C5:C25="Western"),F5:F25))

=sum(if(B5:B25="Northwind",IF(C5:C25="Western"),1,0)))

Also check your help for SUMIF functions.

If this helps you, Please rate me. Thanks, Patty

=sumif(B5:B25,"Northwind",F5:F25)

Here are SUMIF functions for multiple conditions.

=sum(if((B5:B25="Northwind")*(C5:C25="Western"),F5:F25))

=sum(if((B5:B25="Northwind")+(C5:C25="Western"),F5:F25))

=sum(if(B5:B25="Northwind",IF(C5:C25="Western"),1,0)))

Also check your help for SUMIF functions.

If this helps you, Please rate me. Thanks, Patty

Dec 09, 2007 | Computers & Internet

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

Dec 03, 2007 | Microsoft Excel for PC

Yes of course, if you have a little background of visual basic programming that's possible you can enhance your requirements.

Please see link http://www.techonthenet.com/excel/questions/cond_format1.php

Please see link http://www.techonthenet.com/excel/questions/cond_format1.php

Nov 29, 2007 | Microsoft Excel for PC

Jan 08, 2011 | Microsoft Excel 2003 (065-03992) for PC

173 people viewed this question

Usually answered in minutes!

No way that I know how to do that. Better to have a header row with a title of "episodes" and just have the numbers below.

×