grannylib
(1000+ posts)
Send PM |
Profile |
Ignore
|
Fri Nov-05-04 05:10 PM
Original message |
Anyone good with Excel formulas? I'm stuck! |
|
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
(1000+ posts)
Send PM |
Profile |
Ignore
|
Fri Nov-05-04 05:13 PM
Response to Original message |
|
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
(1000+ posts)
Send PM |
Profile |
Ignore
|
Fri Nov-05-04 05:20 PM
Response to Reply #1 |
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
(1000+ posts)
Send PM |
Profile |
Ignore
|
Fri Nov-05-04 05:26 PM
Response to Reply #2 |
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
(1000+ posts)
Send PM |
Profile |
Ignore
|
Fri Nov-05-04 05:30 PM
Response to Reply #5 |
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
(1000+ posts)
Send PM |
Profile |
Ignore
|
Fri Nov-05-04 05:32 PM
Response to Reply #7 |
|
That's not going to work.
Are you trying to add 1 to the next column?
|
grannylib
(1000+ posts)
Send PM |
Profile |
Ignore
|
Fri Nov-05-04 05:38 PM
Response to Reply #9 |
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
(1000+ posts)
Send PM |
Profile |
Ignore
|
Fri Nov-05-04 05:43 PM
Response to Reply #11 |
17. You'd be better off adding a new worksheet |
|
for each month instead of typing over each month's figures.
|
VelmaD
(1000+ posts)
Send PM |
Profile |
Ignore
|
Fri Nov-05-04 05:50 PM
Response to Reply #11 |
19. I agree about doing a seperate sheet each month, but... |
|
Edited on Fri Nov-05-04 05:51 PM by VelmaD
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
(1000+ posts)
Send PM |
Profile |
Ignore
|
Fri Nov-05-04 06:00 PM
Response to Reply #19 |
20. Ah ok, I will try that on my little made-up-just-for-the-occasion one |
grannylib
(1000+ posts)
Send PM |
Profile |
Ignore
|
Fri Nov-05-04 06:04 PM
Response to Reply #19 |
21. Whoohoo, that worked! Cool beans, thanks much for that one! |
VelmaD
(1000+ posts)
Send PM |
Profile |
Ignore
|
Fri Nov-05-04 06:06 PM
Response to Reply #21 |
|
GO ME!
Seriously, glad it worked. Let me know if you manage to decipher the instruction from my pm. :)
|
Blue-Jay
(1000+ posts)
Send PM |
Profile |
Ignore
|
Fri Nov-05-04 05:28 PM
Response to Reply #2 |
|
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
(1000+ posts)
Send PM |
Profile |
Ignore
|
Fri Nov-05-04 05:36 PM
Response to Reply #6 |
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
(1000+ posts)
Send PM |
Profile |
Ignore
|
Fri Nov-05-04 05:40 PM
Response to Reply #10 |
|
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
(1000+ posts)
Send PM |
Profile |
Ignore
|
Fri Nov-05-04 06:12 PM
Response to Reply #14 |
23. Ah, ok, instead of a range with a condition....will try that, thanks! |
grannylib
(1000+ posts)
Send PM |
Profile |
Ignore
|
Fri Nov-05-04 05:45 PM
Response to Reply #6 |
18. *lol* it changed everything to 1 in that column! Hee hee...thank god for |
|
UNDO!!!! what a moron i am
|
NV Whino
(1000+ posts)
Send PM |
Profile |
Ignore
|
Fri Nov-05-04 05:20 PM
Response to Original message |
|
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
(1000+ posts)
Send PM |
Profile |
Ignore
|
Fri Nov-05-04 05:41 PM
Response to Reply #3 |
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
(1000+ posts)
Send PM |
Profile |
Ignore
|
Fri Nov-05-04 05:25 PM
Response to Original message |
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
(1000+ posts)
Send PM |
Profile |
Ignore
|
Fri Nov-05-04 05:31 PM
Response to Reply #4 |
8. Sigma only gives you the sum...I think you mean.. |
|
Edited on Fri Nov-05-04 05:32 PM by VelmaD
the button that is usually right next to it. Function button (looks like a lower case f and x)
|
redqueen
(1000+ posts)
Send PM |
Profile |
Ignore
|
Fri Nov-05-04 05:39 PM
Response to Reply #8 |
|
Duh... yeah that one.
Closing our invoicing today. I'm only halfway here.
:crazy:
|
grannylib
(1000+ posts)
Send PM |
Profile |
Ignore
|
Fri Nov-05-04 05:40 PM
Response to Reply #8 |
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
(1000+ posts)
Send PM |
Profile |
Ignore
|
Fri Nov-05-04 05:42 PM
Response to Reply #13 |
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
(1000+ posts)
Send PM |
Profile |
Ignore
|
Fri Nov-05-04 06:24 PM
Response to Original message |
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...:-)
|
DU
AdBot (1000+ posts) |
Thu Apr 18th 2024, 04:25 AM
Response to Original message |