#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 20
Default 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   Report Post  
Posted to microsoft.public.excel.misc
CLR CLR is offline
external usenet poster
 
Posts: 1,998
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,593
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 07:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"