Jump to content

A question about Microsoft Excel


Fatality911

Recommended Posts

Is there more to it that im just not seeing or is it basically all just data entry? I have testing on it tomorrow for a medical job im going in for' date=' so im sorta giving myself a crash course in it.

 

Any advice would be appreciated. Thanks.[/quote']

 

Take the online tutorials. They will give you an overview of the program. Personally I would move on to Access ASAP. Excel is for bean counters.

Link to comment
Share on other sites

LURRRVVVVEEE excel,

 

Its awesome, however I can't really give you a course over the net. I suggest you sit down with someone who knows it fairly well and get them to show you some stuff.

 

It is crazy powerful, and I swear no one person on the planet knows more then 50% of its functions, ITS MASSIVE.

Link to comment
Share on other sites

LURRRVVVVEEE excel' date='

 

Its awesome, however I can't really give you a course over the net. I suggest you sit down with someone who knows it fairly well and get them to show you some stuff.

 

It is crazy powerful, and I swear no one person on the planet knows more then 50% of its functions, ITS MASSIVE.[/quote']

 

I can't do that right now. Im kinda winging this one on my own.

Link to comment
Share on other sites

freezing an entry in a formula by using dollar signs.

 

$B$3

 

 

 

so when you click and drag your equation down the entire column, instead of it looking like this.

 

A1*B1

A2*B2

A3*B3

......

 

 

it will look like this

 

$A$1*B1

$A$1*B2

$A$1*B3

.........

 

A1 doesnt change.

Link to comment
Share on other sites

@ Dcent

 

Is there a purpose to it other than the fact that the dollar signs are there?

 

And im still working on trying to figure out how to add data together' date=' or merge exsisting data with new data.

 

Basically im like a newborn with this.[/quote']

 

 

Add

If yoiu want to add Cell A2 plus Cell E3 type the following exactly as per below

=a2+e3

 

if you want to add cells A1 a2 a3 a4 a5 a6 a7 a8 write

=sum(a1:a8)

or

=a1+a2+a3+a4+a5+a6+a7+a8

Link to comment
Share on other sites

@ Dcent

 

Is there a purpose to it other than the fact that the dollar signs are there?

 

And im still working on trying to figure out how to add data together' date=' or merge exsisting data with new data.

 

Basically im like a newborn with this.[/quote']

 

you have 200 entrys in column a

 

you have 200 entrys in column b

 

you want to add a1 to b1

you want to add a2 to b2

you want to add a3 to b3

 

in c1 type ....... =a1+b1

and drag it down all 200 cells.

 

 

 

 

BUT..... if you want to add a1 to b1

a1 to b2

a1 to b3

a1 to b4

 

etc..... you have to type it out 200 times,

 

or type ........ $a$1 + b1 and drag it down all 200 cells and it will perform

 

a1 +b1

a1+b2

a1+b3

a1+b4

 

 

 

make sense.

 

 

 

maybe column c is column b + a1

column d is column b + a2

column e is column b + a3

etc.

 

then you use $a$1 etc.

 

 

 

 

trust me...... if you dont get it now, after a day or 2 of working with excel you will get it.

 

 

 

example..... you have 4 different interest rates and you have a column with 240 entrys representing monthly compounded interest over 20 years.

 

 

type the equation once and drag it down all 240 cells....... you WILL have to use $x$x

Link to comment
Share on other sites

you have 200 entrys in column a

 

you have 200 entrys in column b

 

you want to add a1 to b1

you want to add a2 to b2

you want to add a3 to b3

 

in c1 type ....... =a1+b1

and drag it down all 200 cells.

 

 

 

 

BUT..... if you want to add a1 to b1

a1 to b2

a1 to b3

a1 to b4

 

etc..... you have to type it out 200 times' date='

 

or type ........ $a$1 + b1 and drag it down all 200 cells and it will perform

 

a1 +b1

a1+b2

a1+b3

a1+b4

 

 

 

make sense.

 

 

 

maybe column c is column b + a1

column d is column b + a2

column e is column b + a3

etc.

 

then you use $a$1 etc.[/quote']

 

So just use $a$1 + b1, then highlight all the numbers in column b, hit enter, and it will add a1 to everything in the b column?

Link to comment
Share on other sites

So just use $a$1 + b1' date=' then highlight all the numbers in column b, hit enter, and it will add a1 to everything in the b column?[/quote']

 

you have 200 entrys from b1 to b200

 

you want column c to be column b * a1

 

go into cell c1

 

type

 

=$a$1*b1

 

(or you can click on b1)

 

 

hit enter..... now c1 has a number in it.

 

click on c1 so it is selected........ there is a black box on the bottom right corner of the highlighted cell.

 

then in the bottom right corner of c1 if you hover your cursor will become a +sign..... click the bottom right corner of cell c1 and drag it down to c200

 

 

this will fill every cell from c1 to c200 with the same equation that is in c1 (the dollar sign will make a1 stay the same but b1 will change to b2, b3, etc.)

 

 

 

also, type 1

2

3

4

5

and then do same thing, highlight the column (1,2,3,4,5) and bottom right corner + sign click and drag down to fill with numbers 1,2,3,4,5,6,7,8,9,10....etc. to 2000 if you want (or whatever the limit is, I think it is near 80 000)

Link to comment
Share on other sites

equations are typed in exactly like this

 

 

=a1+a2

=a1*a2

=a1-a2

=a1/a2

=a1^a2

=exp(a1)

etc.

 

 

sometimes order of operations ****s up so use brackets ALOT.

 

The majority of what im going to be using this for is medical analysis, medical history, and medical billing. I've got the basics of it for the most part, but the combining data is still giving me some trouble. I'm working on what you just told me now.

Link to comment
Share on other sites

The majority of what im going to be using this for is medical analysis' date=' medical history, and medical billing. I've got the basics of it for the most part, but the combining data is still giving me some trouble. I'm working on what you just told me now.[/quote']

 

post some of the equations you want to do.....

 

 

if you have an equation you want to drag down over 100cells then excel is usefull (for repeated calcs)

 

 

I will be back later and tell you exactly what to type.

Link to comment
Share on other sites

Ok.

 

Let's say John Smith has been billed 500.00 for his first visit, 2,500.00 for his second, and 1,200.00 for his third. He made all of these visits in the span of five months and now i have to average out how often he came in as well as what the average he was billed is.

 

Also i need to put his medical history and list of medications he is currently on in, so that it can be used as a reference when treating him. That way the doctors don't give a medicine that will react negatively to what he is already on, or so they can look at past medical history to see if the new visits symptoms coinside with the old ones.

 

Make sense?

 

Basically what i need to make with this is a virtual medical data bank on the patient.

Link to comment
Share on other sites

Ok.

 

Let's say John Smith has been billed 500.00 for his first visit' date=' 2,500.00 for his second, and 1,200.00 for his third. He made all of these visits in the span of five months and now i have to average out how often he came in as well as what the average he was billed is.

 

Also i need to put his medical history and list of medications he is currently on in, so that it can be used as a reference when treating him. That way the doctors don't give a medicine that will react negatively to what he is already on, or so they can look at past medical history to see if the new visits symptoms coinside with the old ones.

 

Make sense?

 

Basically what i need to make with this is a virtual medical data bank on the patient.[/quote']

 

 

 

 

 

=average(hilight the cells with dollar values in them...... and the brackets ARE required)

 

 

 

average # of visits per year?

 

 

 

=count(highlite the row of cells containing all the dates of his visits for 1 year..... again the brackets are required)

 

once you have a column of numbers using the count function (say 5,7,4 for years 07,08,09)

 

then use the average function on this column like you did for cost.

 

 

 

 

here.......

 

the equations are below the cell they are used in

 

excelexample.jpg

Link to comment
Share on other sites

=average(hilight the cells with dollar values in them...... and the brackets ARE required)

 

 

 

average # of visits per year?

 

 

 

=count(highlite the row of cells containing all the dates of his visits for 1 year..... again the brackets are required)

 

once you have a column of numbers using the count function (say 5' date='7,4 for years 07,08,09)

 

then use the average function on this column like you did for cost.

 

 

 

 

here.......

 

the equations are below the cell they are used in

 

[img']http://i169.photobucket.com/albums/u216/dcentc/excelexample.jpg[/img]

 

Ok, that makes alot more sense to me. The problem that I was having was getting all of them seperated and then putting them together based on month, semi-annual, and annual. But I finally got that figured out.

 

Thanks man.

Link to comment
Share on other sites

another common tool excel can be used for that almost no people do is this calculate a payment schedule for a loan' date=' car loan, or mortgage.[/quote']

 

I made one of those.

 

made a tax spreadsheet too.

 

 

 

 

the if functions I can half assed ...... and the OP is pretty green so I didnt want to get into IF /OR functions or anything too retarded.

 

 

doesnt sound like he needs it.

Link to comment
Share on other sites

I made one of those.

 

made a tax spreadsheet too.

 

 

 

 

the if functions I can half assed ...... and the OP is pretty green so I didnt want to get into IF /OR functions or anything too retarded.

 

 

doesnt sound like he needs it.

 

but every needs to know how to do match function inside an index function inside an iferror function! lol.

 

if nested function I put up there is incredible for me! literally has saved me hours of tedious work that I could easily mistype.

 

to me it's the best 'return value' type of function out there.

 

for example we have several thousand codes that receive funding and several thousand that are stagnant and when I need to determine if we are deducting all the ones with funding in them. it's just craziness over here

Link to comment
Share on other sites

I made one of those.

 

made a tax spreadsheet too.

 

 

 

 

the if functions I can half assed ...... and the OP is pretty green so I didnt want to get into IF /OR functions or anything too retarded.

 

 

doesnt sound like he needs it.

 

what's nice is to use the pmt function in determining personal budget or company budget. when using that function you can play with interest rates that way you have a better understanding of what your monthly payment will be with a certain interest rate which gives you more competence in negotiating. also helps with the term of a loan and how fast you can pay it off when you pay more than the payment amount.

 

it's frustrating when you see how much ****ing interest pay though... lol

Link to comment
Share on other sites

Just hope you don't have to use Macros' date=' else your ****ed my friend[/quote']

 

you serious? Macros are easy!

 

Developer tab => Record Macros

 

Then go through the process and stop recording. Save the macro and rerun at your own leisure!

 

only bad thing is that you can only use macros for common uses and procedures that you do several times in order to save time.

 

For example if you have subtotal a couple thousand program codes, then copy and paste value on another sheet sheet and then sort by program code you can do that and it'll be correct but you have to do things that are specific for that particular project macros wouldn't be useful.

Link to comment
Share on other sites

you serious? Macros are easy!

 

Developer tab => Record Macros

 

Then go through the process and stop recording. Save the macro and rerun at your own leisure!

 

only bad thing is that you can only use macros for common uses and procedures that you do several times in order to save time.

 

For example if you have subtotal a couple thousand program codes' date=' then copy and paste value on another sheet sheet and then sort by program code you can do that and it'll be correct but you have to do things that are specific for that particular project macros wouldn't be useful.[/quote']

 

Yes that's easy, involving VBA in your Macros is another thing.

Link to comment
Share on other sites

I program in VBA... normal macros can get out of hand... VBA code doesn't reside on the sheet itself, it is hidden from the user, and I personally like it more then excel formulas/functions. You can also make custom functions with VBA. It gives an overall clean look to spreadsheets, no data clutter, less chance for error due to user interference with code.

Link to comment
Share on other sites

you serious? Macros are easy!

 

Developer tab => Record Macros

 

Then go through the process and stop recording. Save the macro and rerun at your own leisure!

 

only bad thing is that you can only use macros for common uses and procedures that you do several times in order to save time.

 

For example if you have subtotal a couple thousand program codes' date=' then copy and paste value on another sheet sheet and then sort by program code you can do that and it'll be correct but you have to do things that are specific for that particular project macros wouldn't be useful.[/quote']

 

this is where VBA would be very useful... VBA is portable across different spreadsheets. It doesn't care what cell your data resides in... with a normal macro your data has to be in a particular cell and a particular format.

 

A lot of spreadsheet templates are available online, just google "mortgage calc spreadsheet"...

Link to comment
Share on other sites

this is where VBA would be very useful... VBA is portable across different spreadsheets. It doesn't care what cell your data resides in... with a normal macro your data has to be in a particular cell and a particular format.

 

A lot of spreadsheet templates are available online' date=' just google "mortgage calc spreadsheet"...[/quote']

 

yeah I understand that. I also understand VBA is very useful but it can get messy as well lol. I don't bother with it because I don't have the need for it... yet I assume.

Link to comment
Share on other sites

Excel is an incredible program if you know it inside and out. I'm a pretty good user, but I've worked with people who know everything about it - and blew me away.

 

You might be able to teach yourself basics about it, which are pretty easy......but if you need to have advanced knowledge, good luck. Takes a bit of time.

Link to comment
Share on other sites

I've worked with people who know everything about it - and blew me away.

 

Impossible I say, no one knows all of excel it is simply not possible.

Its like no one knows all of an ERP system like SAP, by the time you have learnt all the existing a new feature packs/add ins come out.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...