Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help
Hopefully, this is the last time I will be posting requesting a formula for
this spreadsheet I have been working on all week. I have a worksheets which shows all the employees that were hired and the ones that left the company. One of my column (H) does a count where it adds 1 for arrivals and substract 1 for departures. I need to be able to know what that number is at the end of each month. My columns are the following: A = Office B = Employee ID C = Name D = Arrivals E = Status (TRUE for arrivals and FALSE for departures) F = addition or substraction (1 or -1) G = Count So basically, if column E is the last date of the month, I need to have a formula which will take the number in column H. Here is a portion of the date, in case it would help create the formula I need. 1185 LON 16106 Doe, Jane 2007-01-01 TRUE 1 1186 MTL 17063 Doe, John 2007-01-02 FALSE -1 1185 OTT 18014 Smith, John 2007-01-02 TRUE 1 1186 MTL 18067 Smith, Jane 2007-01-03 TRUE 1 1187 OTT 18072 Wilson, John 2007-01-03 TRUE 1 1188 MTL 16811 Wilson, Jane 2007-01-05 FAUX -1 1187 MTL 17699 Smith, David 2007-01-31 TRUE 1 1188 I would need 1188 to be the result of the formula Thanks for your help Jeannie |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help
If you are wanting a running total in Column G, then assuming your last entry
is on row 100, then in G101 you would put this, and copy down as rows are added..... =G100+F101 Vaya con Dios, Chuck, CABGx3 "Jeannie" wrote: Hopefully, this is the last time I will be posting requesting a formula for this spreadsheet I have been working on all week. I have a worksheets which shows all the employees that were hired and the ones that left the company. One of my column (H) does a count where it adds 1 for arrivals and substract 1 for departures. I need to be able to know what that number is at the end of each month. My columns are the following: A = Office B = Employee ID C = Name D = Arrivals E = Status (TRUE for arrivals and FALSE for departures) F = addition or substraction (1 or -1) G = Count So basically, if column E is the last date of the month, I need to have a formula which will take the number in column H. Here is a portion of the date, in case it would help create the formula I need. 1185 LON 16106 Doe, Jane 2007-01-01 TRUE 1 1186 MTL 17063 Doe, John 2007-01-02 FALSE -1 1185 OTT 18014 Smith, John 2007-01-02 TRUE 1 1186 MTL 18067 Smith, Jane 2007-01-03 TRUE 1 1187 OTT 18072 Wilson, John 2007-01-03 TRUE 1 1188 MTL 16811 Wilson, Jane 2007-01-05 FAUX -1 1187 MTL 17699 Smith, David 2007-01-31 TRUE 1 1188 I would need 1188 to be the result of the formula Thanks for your help Jeannie |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help
=IF(D1=MAX(IF(MONTH($D$1:$D$100)=MONTH(D1),$D$1:$D $100)),G1,"")
which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. Note that you cannot use a whole column in array formulae (prior to excel 2007), but must use an explicit range. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jeannie" wrote in message ... Hopefully, this is the last time I will be posting requesting a formula for this spreadsheet I have been working on all week. I have a worksheets which shows all the employees that were hired and the ones that left the company. One of my column (H) does a count where it adds 1 for arrivals and substract 1 for departures. I need to be able to know what that number is at the end of each month. My columns are the following: A = Office B = Employee ID C = Name D = Arrivals E = Status (TRUE for arrivals and FALSE for departures) F = addition or substraction (1 or -1) G = Count So basically, if column E is the last date of the month, I need to have a formula which will take the number in column H. Here is a portion of the date, in case it would help create the formula I need. 1185 LON 16106 Doe, Jane 2007-01-01 TRUE 1 1186 MTL 17063 Doe, John 2007-01-02 FALSE -1 1185 OTT 18014 Smith, John 2007-01-02 TRUE 1 1186 MTL 18067 Smith, Jane 2007-01-03 TRUE 1 1187 OTT 18072 Wilson, John 2007-01-03 TRUE 1 1188 MTL 16811 Wilson, Jane 2007-01-05 FAUX -1 1187 MTL 17699 Smith, David 2007-01-31 TRUE 1 1188 I would need 1188 to be the result of the formula Thanks for your help Jeannie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|