Excel Help

2010-11-04 13:03:17

#1

Sisco: grandmaster league revivalist; +493|6735

Hi guys,

I need some help with an Excel sheet for work. Despite all my collegues and myself put "MS Office Knowledge" in our CV, none of us got a clue. lol

Anyways, it´s for our monthly statistics. The daily arrivals and bednights for the hotel I´m working at.

On the left are the days. The formula in the columns A and B needs to be adjusted to get a hold on the newly added countries (Saudi Arabien, Vereinigte Arabische Emirate, Brasilien)

Problem is, I can´t simply rubberband all the cells , cause column A needs the sum of all the left fields in a line, column B the right fields.

Is there a way to adjust the formula the smart way or do we have to add all days and fields manually?

karma for help ofc!

http://rapidshare.com/files/428920431/W … 0.2010.xls

Thanks guys!

https://www.abload.de/img/bf3-bf2ssig0250wvn.jpg

2010-11-04 13:10:47

#2

Bevo: Nah; +718|6913|Austin, Texas

I have no idea what you're asking for, sum wise

Could you try and re-explain?

2010-11-04 13:14:25

#3

Sisco: grandmaster league revivalist; +493|6735

I´ll make a pic, wait.

2010-11-04 13:19:09

#4

Sisco: grandmaster league revivalist; +493|6735

https://www.abload.de/img/zwischenablage02vlcw.jpg

In this pic, columns Y and AA should be added into the formula on the left, column C.

Same goes for columns X and Z to be added into formula in column B.

Is there a smart way to do that? Cause I have 31 lines to add

2010-11-04 13:20:02

#5

Bevo: Nah; +718|6913|Austin, Texas

If you add the newly added countries to your formula and just copy /shift paste down the column, it will add the new countries to your formula for every row. Which is pretty easy if you only need to add those 4, I've done it already.

I don't know off the top of my head a way to tell it to sum every other column, though. This is a bit of an odd setup for a spreadsheet.

Just click into the formula for the top one, and you should see a bunch of colored boxes appear over the cells it's adding. Add a plus to the end of the formula and click on all new boxes. When you're done, copy it, and shift paste down the columns and it should be updated for the lot of them.

2010-11-04 13:21:08

#6

SenorToenails: Veritas et Scientia; +444|6522|North Tonawanda, NY

This might help?

http://excel.bigresource.com/Track-excel-xhAFkRhe/

2010-11-04 13:25:33

#7

Bevo: Nah; +718|6913|Austin, Texas

If you just wanted to add those few, I've already done it, dunno where to upload the file.

2010-11-04 13:29:00

#8

mtb0minime: minimember; +2,418|7046

And if it gets to the point where you need to add many many more, you may want to consider rearranging the table Though I'm not exactly sure how...

2010-11-04 13:30:13

#9

Bevo: Nah; +718|6913|Austin, Texas

http://dl.dropbox.com/u/14463524/Copy%2 … 0.2010.xls

That should be it.

2010-11-04 13:31:07

#10

Sisco: grandmaster league revivalist; +493|6735

Nah, I need all 31 lines. I´m not sure I made the problem clear.

The formula in C1 needs the cells Y1, AA1 and EC1 added.

The formula in C2 needs Y2, AA2 and EC2.

this all the way down to C42 and the same for B1. That what you did?

I´m totally retarded for excel

2010-11-04 13:32:07

#11

Sisco: grandmaster league revivalist; +493|6735

Bevo wrote:
http://dl.dropbox.com/u/14463524/Copy%20of%20WTV_VORLAGE_erweitert_aktualisiert_am_28.10.2010.xls

That should be it.

Yeah, thats it! How did oyu do that?

2010-11-04 13:33:35

#12

Bevo: Nah; +718|6913|Austin, Texas

Sisco wrote:
Nah, I need all 31 lines. I´m not sure I made the problem clear.

The formula in C1 needs the cells Y1, AA1 and EC1 added.

The formula in C2 needs Y2, AA2 and EC2.

this all the way down to C42 and the same for B1. That what you did?

I´m totally retarded for excel

Yep, that's what I did

Excel is smart enough to know that your "sums" change by line. You'd have to enter $A$1 for it to take data from cell A1 each time, otherwise it changes with row.

(If you changed the axis' on the spreadsheet, this would have been 100 times easier)

2010-11-04 13:35:26

#13

Sisco: grandmaster league revivalist; +493|6735

mtb0minime wrote:
And if it gets to the point where you need to add many many more, you may want to consider rearranging the table Though I'm not exactly sure how...

I know, the whole system it totally fucken retarded. Thing is, is a really small hotel (21 rooms) and the boss is real oldschool. For all these things (booking, statistics, whatever) a modern hotel uses software which makes handling these things pretty easy. It´s expensive though, so he keeps the old system he used 20 years ago

2010-11-04 13:36:42

#14

Sisco: grandmaster league revivalist; +493|6735

Bevo wrote:
Sisco wrote:
Nah, I need all 31 lines. I´m not sure I made the problem clear.

The formula in C1 needs the cells Y1, AA1 and EC1 added.

The formula in C2 needs Y2, AA2 and EC2.

this all the way down to C42 and the same for B1. That what you did?

I´m totally retarded for excel
Yep, that's what I did

Excel is smart enough to know that your "sums" change by line. You'd have to enter $A$1 for it to take data from cell A1 each time, otherwise it changes with row.

(If you changed the axis' on the spreadsheet, this would have been 100 times easier)

I´m not sure what that means. Could you do me the favor and add the lines? Pretty please?

2010-11-04 13:38:16

#15

Bevo: Nah; +718|6913|Austin, Texas

Sisco wrote:
Bevo wrote:
http://dl.dropbox.com/u/14463524/Copy%20of%20WTV_VORLAGE_erweitert_aktualisiert_am_28.10.2010.xls

That should be it.
Yeah, thats it! How did oyu do that?

Click on B12, fix it so that all pieces are added correctly, hit enter
Click on B12, copy
Highlight B13-B42, paste
Click on C12, fix it
Copy, Highlight C13-C42, paste

presto

2010-11-04 13:44:38

#16

mtb0minime: minimember; +2,418|7046

Engineers, more fluent in Excel than accountants and statisticians

2010-11-04 13:45:36

#17

Sisco: grandmaster league revivalist; +493|6735

Bevo wrote:
Sisco wrote:
Bevo wrote:
http://dl.dropbox.com/u/14463524/Copy%20of%20WTV_VORLAGE_erweitert_aktualisiert_am_28.10.2010.xls

That should be it.
Yeah, thats it! How did oyu do that?
Click on B12, fix it so that all pieces are added correctly, hit enter
Click on B12, copy
Highlight B13-B42, paste
Click on C12, fix it
Copy, Highlight C13-C42, paste

presto

How do I "fix" B12?

2010-11-04 13:48:01

#18

Bevo: Nah; +718|6913|Austin, Texas

Change the formula to whatever you need

so for example on your old one. Click on B12, then go up to the formula bar and click on that. All the cells being added are now outlines in pretty colors. Then type at the end (or the middle, or wherever) a plus sign and then click on a box you need to be added. Repeat until you've got all the boxes you need to be added for that line. When you're done, click enter. Then follow the rest of the steps

2010-11-04 13:48:41

#19

mtb0minime: minimember; +2,418|7046

$ in front of columns will fix the column. So $B12 means that it will perform the operation on column B, and then the corresponding line (doing operations on line 13 will use B13).

$ in front of rows will fix the row. So B$12 means that it'll operate on line 12 and the corresponding column.

To use B12 no matter what, $B$12 will fix both the column and the row.

2010-11-04 13:50:54

#20

Bevo: Nah; +718|6913|Austin, Texas

mtb0minime wrote:
Engineers, more fluent in Excel than accountants and statisticians

fuck excel.

I used to think making graphs and shit for physics in highschool was tough. Now I do 3-4 of them per lab in about 5 minutes each.

2010-11-04 13:52:23

#21

Sisco: grandmaster league revivalist; +493|6735

Ah, okay, that doesn´t sound to complicated. If you know how, thats easy.

Thank you so much guys, especially Bevo, you helped us out big time!

Last edited by Sisco (2010-11-04 13:52:47)

2010-11-04 13:52:41

#22

SenorToenails: Veritas et Scientia; +444|6522|North Tonawanda, NY

Bevo wrote:
mtb0minime wrote:
Engineers, more fluent in Excel than accountants and statisticians
fuck excel.

I used to think making graphs and shit for physics in highschool was tough. Now I do 3-4 of them per lab in about 5 minutes each.

MATLAB/Octave and R are far more powerful for plotting...but excel has that nice GUI, lol

2010-11-04 13:55:03

#23

mtb0minime: minimember; +2,418|7046

Hah, yeah I've used matlab a bit. And even though it'll take a 1000-character formula to get something done in excel, it's still faster for me to do that than deal with matlab

Speaking of which, I need to download it on my computer and practice with it. I've considered screwing around with it to devise some sort of program that'll read an excel file of user names and then output with the corresponding link to bf2s profile, xbl, and psn profiles, for use in that gamer id database. /nerd

Last edited by mtb0minime (2010-11-04 13:57:12)

2010-11-04 13:55:28

#24

Bevo: Nah; +718|6913|Austin, Texas

SenorToenails wrote:
Bevo wrote:
mtb0minime wrote:
Engineers, more fluent in Excel than accountants and statisticians
fuck excel.

I used to think making graphs and shit for physics in highschool was tough. Now I do 3-4 of them per lab in about 5 minutes each.
MATLAB/Octave and R are far more powerful for plotting...but excel has that nice GUI, lol

We use fucking kaleidagraph for our physics lab, some old outdated "excel" with shitty gui, but the fit lines are easy.

Used matlab a bit during my programming course. Dunno about plotting anything in it

Last edited by Bevo (2010-11-04 13:56:06)

2010-11-04 14:05:21

#25

SenorToenails: Veritas et Scientia; +444|6522|North Tonawanda, NY

mtb0minime wrote:
Hah, yeah I've used matlab a bit. And even though it'll take a 1000-character formula to get something done in excel, it's still faster for me to do that than deal with matlab

Speaking of which, I need to download it on my computer and practice with it. I've considered screwing around with it to devise some sort of program that'll read an excel file of user names and then output with the corresponding link to bf2s profile, xbl, and psn profiles, for use in that gamer id database. /nerd

If you are science/engineering and dealing with numerics, it is definitely worth learning (or at the very least, becoming comfortable with).

BF2S Forums Excel Help

Bevo wrote:

Sisco wrote:

mtb0minime wrote:

Bevo wrote:

Sisco wrote:

Sisco wrote:

Bevo wrote:

Bevo wrote:

Sisco wrote:

Bevo wrote:

mtb0minime wrote:

Bevo wrote:

mtb0minime wrote:

SenorToenails wrote:

Bevo wrote:

mtb0minime wrote:

mtb0minime wrote:

Board footer