Anyone good with Excel formulas? I'm stuck!

[grannylib]

I have a spreadsheet with an employee roster; problem is folks from different areas sometimes work under a sup from another area, and they are grouped by sup, not worktype. I need to be able to do a couple of things:
1. figure out an average from a column of months of service, but only for a worktype (for which I have created another column, with single letters ie 'M'denoting the worktype.) So say if C is the column with the months, and E is the column with the worktype, I think it's something like:
=AVERAGE((C1:C185)*IF(E1:E185,"*M*"))
but that's not quite it.
I also need to figure out how to get the C column to automatically add 1 to the number in each cell when I do it each month so I don't have to do it manually.

Thanks bunches in advance for any assistance!
grannyB-)

[Blue-Jay]

1. RE: Your C Column -

Just enter the formula "={up arrow} +1" in the second row. Then drag the formula down to C185.

The other, I think I'd need to take a look at.

[grannylib]

2. Oh, thanks, that one will help! Hang on...what do you mean by the second

row? Of the spreadsheet itself, or of one of those dialog box thingies?? Or in the second cell of the column?
Sorry to be such a diz - I am not much of a math person and am not terribly well-acquainted with Excel.

[catzies]

5. Your very first cell needs to have a "hard number" in it. Then the

formula will work in each row underneath by referencing the row above's value plus one.

E.G. in cell A1 type "1." In cell A2, type "={click cell A1}, then the plus sign, then the number one, and hit enter.

The value of the cell is 2, but the contents of the cell reads "=A1+1"

I would use the AutoFilter too if this were my spreadsheet.

[grannylib]

7. Oh ok. It's not going to need to be sequential or anything, right?

Like if my column of numbers reads:

54
2
463
41
26
13
11
3
564
44

that formula will add 1 to each of them and not screw 'em up?

[Blue-Jay]

9. Whoa! Hold on!

That's not going to work.

Are you trying to add 1 to the next column?

[grannylib]

11. No I have a column of numbers and I want to add 1 to each cell in that

column to indicate the accrual of an additional month of service, so I don't have to manually change the number in each cell.
I'm sorry, I am really a novice at this and am probably not explaining it very well.

[Blue-Jay]

17. You'd be better off adding a new worksheet

for each month instead of typing over each month's figures.

[VelmaD]

19. I agree about doing a seperate sheet each month, but...

whether you do that or just want to overtype the curren tmonth here's what you do. Go out into an unused column. Click = and then click back over into the cell with the number of months of service then hit + 1 and Enter. Copy and paste that formula all the way down the sheet and it'll give you the months of service plus one for each row. Then what you do is copy the column and paste it into the column that had the previous months months of service. But use paste special instead of just paste. Paste special will take you to a box where you need to click "values".

Hope that made sense.

[grannylib]

20. Ah ok, I will try that on my little made-up-just-for-the-occasion one

here at home. Thanks!

[grannylib]

21. Whoohoo, that worked! Cool beans, thanks much for that one!

[VelmaD]

22. I ROCK!

GO ME!

Seriously, glad it worked. Let me know if you manage to decipher the instruction from my pm. :)

[Blue-Jay]

6. I meant

that you should enter that formula in cell C2. Also (to be clear), I meant that you should use the actual up arrow, not that you should C&P my example. After you enter the formula, click on C2 and position the cursor over the bottom left of the cell. When a "+" appears, drag the cursor down to the last cell in the column.

I'm not sure exactly what you're trying to do with the other formula. What are you trying to average? I'd need to probably see it to get what number you're trying to come up with.

[grannylib]

10. Thanks for the clarification , I think I gotcha! the other thing I'll try

to explain better.

Amy 43 M
Bob 274 N
Jon 21 N
Abdul 621 B
Meg 12 B
Yan 5 N

Bill 465 M
Jamaal 342 S
Sue 42 S
Miryam 3 S
Ann 5 E
Jake 35 B

Marta 5 M
Nita 375 E
Dave 32 E
Tom 12 E
Marissa 58 B


So I have my months of service in column B here (sorry, wasn't going to do more than I had to! There's like 5 or 6 on the sheet but anyway...)
And then C is the worktype. The groups have to stay in order, under the manager (M) of each group. SO what I want to do is a formula that gives me the average months of service for a particular worktype.
Does that make sense?

[Blue-Jay]

14. OK, OK.

Just use the average formula "=average(B2, B7, Bwhatever)" and you'll have to enter each of the cells that have the number of that particular worktype.

[grannylib]

23. Ah, ok, instead of a range with a condition....will try that, thanks!

[grannylib]

18. *lol* it changed everything to 1 in that column! Hee hee...thank god for

UNDO!!!!
what a moron i am

[NV Whino]

3. Your problem is

that you are trying to use a spread sheet program for a database. In spite of what Mr Gates would have you believe, Excel is not a database.

You would get better (and easier) results if you used a database program such as FileMaker Pro or FoxPro.

[grannylib]

15. Oh ..I will have to check into that. It's just the first week on this job

and I am not sure what software we may have available to us.
Thanks for the tip!

[redqueen]

4. Do you have an AutoSum (sigma) button on your toolbar?

If you click it it should give you a lot of preprogrammed options for common formulas like averaging and such.

[VelmaD]

8. Sigma only gives you the sum...I think you mean..

the button that is usually right next to it. Function button (looks like a lower case f and x)

[redqueen]

12. lol

Duh... yeah that one.

Closing our invoicing today. I'm only halfway here.

:crazy:

[grannylib]

13. Yeah I've tried farting around with that and have searched the help thing

and the web and what not but it's really hard to get specific help.
And yikes, my previous post did not come out all aligned very well *lol*

[VelmaD]

16. I am my department's Excel goddess

If you'd like to send me what you have and an explanation of what you need it to do I might be able to help. Drop me a pm.

[petronius]

24. This should do the averaging thing:

In the cell where you want the average value to appear type:

=SUMIF(e1:e185,"m",c1:B185)/COUNTIF(e1:e185,"m")

where the C column is the values you want to average and the E column is the column containing the work types.

The SUMIF clause will add up all the C cells that are associated with a value of "m" in the E column, while the COUNTIF will just count all the occurences of "m" in the E range. The division will give you the average.

You can then have a single cell to give the average value for each work type.

You should probably experiment to make sure this is what you want before risking your job based on me...:-)