Month and Year

  • Thread starter Thread starter deacs
  • Start date Start date
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
 
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)
 
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
 
Hi
try something like:
IF(C1<>"",calculate_change_formula,"")
and copy this down
Frank
 
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)
 
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
 
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)
 
Back
Top