Register for free to join our community of investors and share your ideas. You will also get access to streaming quotes, interactive charts, trades, portfolio, live options flow and more tools.
so let's learn some useful functions today.......
ever have a spreadsheet full of numbers that has been exported out of a nonformatted system, like an AS400?
in other words it dumps out the raw data.
let's say you are using a 7 digit/character number, but the number was exported without a dash after the 3rd character. there are ways around this, if you want to add one.
what happens to me is i usually work with thousands of numbers and have to get a dash in there quick. one way to do this would be using a few different functions.
so let's imagine that we have 10000 seven character numbers starting in A1 - A10000. you need to put a dash after the 3rd character. let's learn three different functions on this one step.
the first is called LEFT.
simply go to B1 and start your first function.
select LEFT from the f* button.
it will bring up a function screen and ask you to select a cell. you would select A1. you would then hit TAB. this will ask you how many characters you want to grab from the LEFT. so the LEFT command will let you select as many as you like, but you only need the first three. so you would hit 3, then OK or Enter. the function should have made the first three characters appear from A1 in cell B1. you would now copy down the formula all the way to B10000.
now, you would use the same steps above in C1, but only this time you would use RIGHT, instead of LEFT. you would also change how many characters you need. it would go from 3 to 4 because you want the remaining numbers not used in B1.
so follow the steps. above and now B1-B10000 should have the first three numbers of the 7 character number, and C1-C10000 should have the remaining four numbers. so it's broken apart and we want to put it back together but with a dash.
you will want to use a CONCATENATE function. so in D1 you would start this command. it would be found in the f* screen. select the function. all the function does is join info together. so you can literally put anything you want from however many cells you like. in this case though you just want to put the number back together with a dash in between.
you have the CONCATENATE function screen up, it will ask for a cell or info to be used. select B1, then hit TAB, it will ask for more info, but this time you would only place a simple - , then you would TAB again. your final step would be to select C1 and then OK or Enter.
your number should now appear in cell D1, complete with a dash.
maybe you won't need to ever do this, but those functions come in handy for so many other things. hope that was helpful. please feel free to ask for further use with the functions.
Lobo, you are awesome. I will be giving you a call. And that formatting is awesome. I sent you an email via your email address so that we can lock horns. I will be needing your help. I truly do appreciate your assistance. Also, we can use gotomeeting.com if you are familiar with that. I'll chat with you on the phone after I try and plug in these equations.
Peace,
Mark
Glad I could help earlier. Was that what you were looking for in your sheet to look like?
The f* button in Excel will open the many formulas you were asking about in your email. Like I said, you can call tomorrow and we will get you straightened out.
MSC. . .I have that site added to my favs. What is going on right now is that I am taking an accellerated course (graduate level) that requires extensive knowledge of MS Excel. This doesn't bother me that much, but I just moved from a home in TN (with my wife) to a new home in MI, and I have all sorts of things to handle between the two transactions. We have now settled in MI (still LOTS of work to get done on the new home) and we're still trying to sell our home in TN). I'm not going there right now.
Lets put it this way, I need HELP with Excel right now. I have more things on my burner than I can handle and I just do not know much about Excel. But I will by the end of the weekend. I just need a Pro to explain it all to me.
Again, many thanks. I would post my assignments right on this message board but I just don't think that would be cool. I know I need to learn this stuff. And I am. And my other classmates will also be reviewing this thread. So let me make my Excel sheets the best possible. I'll worry about the equations. . .you guys help me with learning excel. I'll see how fast I can get familiar with it. Right now, it appears really cool! But I need Clayton's help on questions pertaining to inserting documents and charts in Excel and with formatting of Excel sheets.
I will have plenty of questions coming in the near future. So, hopefully some of you will be able to be online over the weekend.
Peace,
Excel help offers good information and may provide answers to some of your questions.
Open Excel, click on Help on the top menu or hit the F1 key. Put some keywords such as entering text or charts into the Answer Wizard. A list of related topics should show. Try different keywords or phrases.
Thanks Clayton,
I will send you my problem set and add questions in parenthesis. I don't need you to solve the problems for me, or answer any of the questions as I can do that. I just need to see how to have it formatted in MS Excel.
I'll send you a PM with my email so that you know who is contacting you.
Thank you SO much!
Mark
on my way out the door, i will address this tonight when i get all my activities done.
when you say add, do you mean create spreadsheets and charts in spreadsheets in Excel for your presentation?
what kind of text do you want to add?
the short answer is yes, you can add anything you like from Word.
clayton@mynetwire.com is my email.....
send me the text you're wanting to add and i'll take a look. hard for me to tell you what to do and where without seeing myself.
Lobo. . .once again, thank you both. I have another question. I'm moving forward here and just learning Excel. My instructor wants me to "add spreadsheets" and "charts" to MS Excel. I have tried to add spreadsheets but it just comes up as another page. I'd like to add text so that it can be read in addition with spreadsheets. How do I do this so the presentation looks good? Do I need to put text in a cell and reformat the cell to a different width and height to reveal the text? Or can I create something in Word and then paste it in MS Excel?
I really appreciate this.
TIA,
Mark
Thank you very kindly, MSC! It worked!
Thanks again,
Mark
Precisely. Thank you MSC290, you clearly know your Excel.
Sorry I haven't been quicker about getting to questions, I'm not logged in here as much as I used to be.
Also note that you should have a disk with your purchase of Excel, or IT would have one if it is for work.
Solver for Excel.
Try this:
Open Excel. Click on Tools on the top menu, select add-ins, click on the box for Solver add-in, click on ok. It should prompt you for the disk that has the info for the add-in install.
Good luck.
Where can I find the "Solver" for MS Excel? Is it on my Standard Edition MS Office Software/CD? If not, please post a link.
Thank you.
Mark
No I figured it out!....Excel has to be saved as .CSV file first!
i ran a test for this and i was able to create a new contact list using the same steps you had?
you still having problems?
Trying to Import a list of
ADDRESS CITY STATE ZIPCODE PHONE TOLLFREE FAXNO EMAIL WEBSITEURL
from an Excel file into a folder i have created in Outlook 2000 under contacts. I take these steps in outlook.
File, ImportExport, Ipmort from another program or file, Microsoft Ecxel, Brows & fined the Excel file, when I go to NEXT, I get a message.
The MS Excel File "C:/Doc......." has no named ranges. Use MS Excel to name the range of data you want to import"
Any Idea what I need to do to bring this data into Outlook?
Thanks
sorry to just now see this. looks like someone helped you.
do you also know how to hyperlink?
you can save an email just as it is onto a word document. save the word document onto your computer and then hyperlink to a cell. what is cool about this is after you click the hyperlink it takes you to the word document and then when you click that it takes you to your exact email (even if you delete it).
i use that all the time and it is amazing.
TC,
I wish that I knew of this board before I retired. There are a lot of helpful hints. And by the way, I will still be using them now, as well.
sumisu
Paste the text into the comment box and then rightclick the cell and choose edit comment. You can then resize the comment box by dragging the corners. Now when the reader hoovers over the comment they will see the whole text.
Need help with Excel please.
If I right click on a cell I can place coments. Is it possible to place a link to particular email that resides in the Inbox, or another special box,(OUTLOOK 2000)in an Excel cell?
If not is it possible to copy and past the Email document in the cell without changing the arrengment of cells, but still being able to read the complet email message?
Mr. Gotti THANKS.
If a cure is found, will post it here.
The reason for Firefox are the tabs. I can open 20 or 30 tabs in each window (at least 10 windows). I think there are 27 charts in 1 window and if the links opens in FF it is a lot more convient.
Thanks again.
Take care.........pilgrim
Enjoy the Fireworks
Deal or No Deal?
Going to church doesn't make you a Christian any more
than standing in a garage makes you a car.
sorry I couldn't be of more assistance. never ran into that problem, but i also don't work with firefox.
hope it all works out for you.
Mr Gotti, Thanks,
Didn't work. I want to hyperlink to charts in an Excel Worksheet and they opened in a Firefox tab.
Found a help page on EXCEL that indicated the software (FF) had to be registered before it could be changed. Tried to register FF and did not help.
If all else fails will pick up a QuattroPro and use it. MS has gone to far. IMO!!
Thanks again.
Take care.........pilgrim
Enjoy the Fireworks
Deal or No Deal?
Going to church doesn't make you a Christian any more
than standing in a garage makes you a car.
Looks like that's not supported by firefox. I found other websites
discussing the issue. One said to go to the Netscape website and get the
Excel plugin. But, I'm not finding that anywhere.
I'll keep looking.
Never had to deal with this before, but I am checking it out. I will try and get back with you today pilgrim.
Mr. Gotti
Is it possible to place a chart hyperlink in EXCEL that will open in Firefox? Until this week I placed a hyperlink to charts in an Excel Worksheet and they opened in a Firefox tab. Don't work no more. They open in IE instead. Have gone to the control panel, chosen add or remove, opened tje Set Program Access and Defaults and changed the Use my current Web browser. Restarted and still IE. Changed the Folder Options, File Types URL File to Firefox and same results. Would really like the links to open in Firefox tabs. Same results with MS/Word. XLS files from a year ago do the same.
CAN YOU HELP?????????? TIA!!
Take care.........pilgrim
Hidden Taxes
Petition for Fair Taxes
Going to church doesn't make you a Christian any more
than standing in a garage makes you a car.
Thanks. Now, if I can just find a reason to use that, I'll know how to do it.
Concatenate.
In this case junk had a Column A with a certain number sequence that he wanted to use.
Example......
GTH-12-001
GTH-12-002
GTH-12-003
GTH-12-004
GTH-12-005
GTH-12-006
Basically he was wanting to make this go on for hundreds maybe thousands of lines.
Instead of typing these in, like 98% of people will do, you can use a Concatenate feature.
I would start of with a blank Excel Sheet.
In column B, simply type GTH-
In column C, type 12-
In column D, type 001......(important to note, if this is not formated right it will say 1 and both preceding zeros will be lost. You may need to type ' in front if your are not properly formatted to hold the zero's).
Underneath the 001, in the next cell a quick formula such as =D1+1, and then a copy down to each following cell will give you this .........
001
002
003
004
005
so on and so forth.
By this time you can copy down column B (just GTH- over and over) and column C (just 12- over and over) as far as you want to go down.
You are now ready to use a Concatenate formula.
f* is the functions tool, Concatenate can be found by going there and selecting All, from the top scroll down bar. Scroll down as it is in alphabetical order.
Make sure you are in cell A1.
Once the formula is ready to start, you simply click Cell B, then Tab, click Cell C, then Tab, click Cell D, now hit OK.
Your first formula would read GTH-12-001.
Copy that down as far as you like and what would have taken you hours to type has been done in less than a minute.
I would select the entire row A, right click, select Copy, right click again and select Paste Special, now select Values, now OK.
You have just removed the formula and can now remove columns B, C and D.
A little history lesson for Concatenate. Such a useful tool, it can be used as many cells as you like. Really is a life saver.
Hope that helps answer your question, feel free to ask away.
Uh, Lobo. That didn't help the rest of us any. We're all tryin' to learn Microsoft Excel. That's why we have this thread bookmarked.
s'plaination, please. Thanks.
No problem
thank you much!
Hooked you up on the phone call and sent it back to you. Concatenate formula is what I used.
Quick and very much a life saver in my work.
you around? I have an excel question?
No sweat!
Don't forget I have a work-around by external processing. It won't be the end of the world if we can't correct the problem right in Excel. Don't drive yourself ape with it.
Thanks,
Fred
Hi;
I think i have figured out the problem! When importing from a "comma dilimited" file into address book, and no Country has been chosen the entery for FAX# wont be based on USA settings, that is (123)456-7890, area code and rest of the number, and it will go to another countries settings.Also if in comma delimited file no paranthesis is place around area code in the form of (123)then no area code is chosen and all the 10 digit is red as a local number Consequently when modem is dialing it wont dial +1(123)456-7890 and the fax will not be sent. I can fix the problem by going into "contacts" double cliking Fax# a window opens up, and from there I can place corrected Fax#. The question I have now is in WinXP is there a setting for country so by defult all settings for phone and Fax are for USA? And most comma dilimited files dont place paranthesis around area code. Any way to get around this?
Thanks
Shawn
I am not ignoring you sir. I have had about a total of 10 minutes to look over the charts. Let me take a gander here and try to get back with you soon. I have been gone for the better part of 24 hours, and my day yesterday was packed with my penny stock failure.
I had a really busy day yesterday and have been gone all day today. I think someone helped you out, but let me know if that didn't work.
Thank you TC;
While my list was imported into address book from a comma dilimmited file, the problem does not appear to be with importing into address book! Even when I add a name and Fax# by hand to a list and then try to bring the COMPLET list into XP fax program (rather than one by one typing them in) still the +1 is missing from the begining of dilaling area code. So at this point I think the problem is with adding the fax#s from a list. Any other suggestion appreciated.
Thanks
Shawn
This says its for Outlook 97 but maybe it will lead you in the right direction
http://support.microsoft.com/?kbid=164408
Hi,
I sent the chart this morning. Did you get it OK?
Fred
Hi...Good to see this board is forming. My question is not directly related to Excel, but I hope you still can help.
i have "OFFICE 2000", I am setting WinXp pro to send faxes. In the "send fax wizard" if I enter each fax# individualy with area code and number like (516)555-1212 it gets enered in the list of # for the fax to be sent to as +1(516)555-1212 and the fax will make it to that#. But if I Import the fax # from "OUTLOOK" address book,they get entered as 516-555-1212 without +1 and paranthesis and the fax won't make it. Any one knows how the addrees book should be set or how rules should be set for me to be able to import# from address book "correctly" and send the faxes?Thanks
Shawn
Can you please send me the chart you are working on?
clayton@mynetwire.com
I just received your message, let me work on it a bit and get back with you.
When charting, I have a problem because Excel arbitrarily sets the y-axis to 0 (zero) under certain circumstances. In trying to solve the problem, I found the material at the bottom of this note which I'm including in the hope it helps clarify the issue. I've highlighted the clause that causes the grief.
At present, I process the data externally, determine if the difference between yMax and yMin exceeds 16.667% of yMax. If so, I add an offset to the values. Needless to say, this is a silly way to work, but I haven't been able to find a way to suppress the highlighted rule.
Do you know any way it can be done that a layman can implement.
Thanks,
Fred
SOURCE MATERIAL:
When you create a chart in Microsoft Excel, there are three
possible scenarios that may apply to your data:
* The yMax and yMin values are both non-negative
(greater than or equal to zero).
This is Scenario One.
* The yMax and yMin values are both non-positive
(less than or equal to zero).
This is Scenario Two.
* The yMax value is positive, and the yMin value is negative.
This is Scenario Three.
The major unit used by the y-axis is automatically determined
by Microsoft Excel, based on all of the data included in the
chart. The following scenarios use this default major unit.
Scenario 1:
Microsoft Excel 97 and Microsoft Excel 98 Macintosh Edition
In Microsoft Excel 97 and Microsoft Excel 98 Macintosh Edition,
the following applies:
* If the chart is a 2-D area, column, bar, line or x-y scatter
chart, the automatic maximum for the y-axis is the first major
unit greater than or equal to the value returned by the
following equation:
yMax + 0.05 * ( yMax - yMin )
Otherwise, the automatic maximum for the y-axis is the first
major unit greater than or equal to yMax.
* If the difference between yMax and yMin is greater than 16.667
percent of the value of yMax, the automatic minimum for the
y-axis is zero.
* If the difference between yMax and yMin is less than 16.667
percent of the value of yMax, the automatic minimum for the
y-axis is the first major unit less than or equal to the value
returned by the following equation:
yMin - ( ( yMax - yMin ) / 2 )
Exception: If the chart is an x-y scatter or bubble chart, the
automatic minimum for the y-axis is the first major unit less
than or equal to yMin.
well i hope i answered your question somewhat......you can do a lot with that autofilter
i didn't know about the auto filter. cool feature.
Sandy
Okay, you cannot count colored cells, but what I do...........
When I am coloring them for a reason I will make the right column next to it with some sort of indicator......whether it be an x or a number....I will then hide it if need be.
I will then use my auto filter, grab the x and highlight the column.
In the right lower corner of your screen it should tell you how many you have highlighted......do you know about that feature?
hi lobo,
i work with excel a lot and always run into problems. wish i found this board a few months ago. anyway, is there a way to make a cell count as 1 if the cell is a certain color?
i have a spread sheet - 4 columns
columns one thru three (77 rows long) have either pink or blue fill color depending on the information within. the fourth column i manually enter total of blue cells in that row. is there a format for that 4th column to automatically count blue cells for each row?
tia,
Sandy
Volume | |
Day Range: | |
Bid Price | |
Ask Price | |
Last Trade Time: |