Month and Year

D

deacs

Is there a way to have Exel only pick out a number from a column if th
corresponding cell in another column is the end of the month or year
For example in column A, I have the "Date" in "1/2/1984" format. I
column B, I have "Account Balance." Is it possible in column C to sho
the Account Balance only if it is the end of the month or end of th
year in column A?

Thanks in advance
 
B

Bob Phillips

End of month

=IF(MONTH(A1)<>MONTH(A1+1),B1,"")

you won't need to test end of year, as end of year is also end of a month.
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
D

deacs

Bob,

Thanks so much for the help! It worked like a charm. Now, the next ste
for me is to figure out the % change in column C from month to mont
without having to manually look to see if column C contains any data
So, if column C has data, I'd like Excel calculate the monthly chang
in account balance. I assume it would just be another if statement, bu
I have been unable to make it work out right to account for th
different number of days in the months
 
F

Frank Kabel

Hi
try something like:
IF(C1<>"",calculate_change_formula,"")
and copy this down
Frank
 
B

Bob Phillips

Deacs,

Is that a request?

How about in D2

=IF(OR(C2="",C3=""),"",(C3-C2)/C2)

although I think this may give you a problem if you have non-month-ends
interspersed, you are likely not to get any results in D1. I think I know
what you want, but I won't mention that.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
D

deacs

Yes, the problem is that I have cells that are non month en
interspersed. And yes, I was requesting help, but no worries, I'll jus
try to work it out. Worst case, I just manually go down the column an
input the % change formula. Thanks again for your help
 
B

Bob Phillips

Don't be daft, what are computers for.

Try this.

In E2, not E1 note, put this array formula (that is confirm with
Ctrl-Shift-Enter, not just Enter)
=INDIRECT(CHAR(COLUMN($C$1)+64)&MAX(ROW($1:1)*(C$1:C1<>"")))
and copy down. Some of the initial rows might show #REF, don't worry

In D2, put this formula
=IF(AND(C2<>"",NOT(ISERROR(E2))),(C2-E2)/E2,"")
and copy down.

Column D should now just should percentage movements for the month end
dates, all others left blank, and pick up the previous month-end figure.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top