Help with calculating by month

  • Thread starter Thread starter X
  • Start date Start date
X

X

I would like to know if there is a way that Excel can pull the current Month
numeric value off of the computer system. I am working with figures that
need to be divided by the month that I am currently in, So that I can get a
rolling monthly average. I know I can just pick a cell and enter the number
each month myself, but I was wondering if Excel could do it automatically by
looking at the system date and returning the numeric value for the current
month. Like 1 for Jan., 2 for Feb., 3 for Mar. and so on.

Thanks
Rick
 
If you meant average for the month of July

=AVERAGE(IF(MONTH(A2:A100)=MONTH(TODAY())*(ISNUMBER(A2:A100)),B2:B100))

where A has the dates and B the numbers

if you want average from the start until the last date in current month

=AVERAGE(OFFSET($B$2,,,MATCH(MAX(IF(MONTH(A2:A100)=MONTH(TODAY())*(ISNUMBER(
A2:A100)),A2:A100)),A2:A100,0)))

both formulas entered with ctrl + shift & enter

the isnumber is to make sure there are numeric dates or else a blank cell
would be counted as Jan
if you know there are no blanks you can remove the isnumber part

Finally if you only want to get the index number for the current month use

=MONTH(TODAY())

entered normally

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Back
Top