compare values in two different columns in a table

[Guest]

I have a table which lists costs associated with various projects.

The columns are:

June | July | August | ... |December

and the rows are the costs of individual projects over these months.

Our rule is that if JulyCost > JuneCost, OK, else go back to project
managers and explain variance.

So, what I would like to do is create some sort of lookup function, in which
I enter today's date, and then Excel compares this month's costs to the
previous month's costs. "IF this month's costs are greater than or equal to
last month's costs, THEN "OK", ELSE "check with project managers" "

Clear?

 

[Guest]

Dave,
Assumes Project IDs are in column A, and dates are in row 1,
column B onwards B1 to N1 in my example). Date are in 01/mm/yy (or
mm/01/yyyy) format.
There is no need to enter a date if you want to use TODAY('s).

Put this in desired column and copy down.

=IF(OFFSET(A2,0,MATCH(DATE(YEAR(TODAY()),MONTH(TODAY()),1),B1:N1,0))>OFFSET(A2,0,MATCH(DATE(YEAR(TODAY()),MONTH(TODAY()),1),B1:N1,0)-1),"OK","Check
with program manager")

HTH

 

[Guest]

Thanks, looks promising.

 

[Guest]

OK, the formula works as you have it, however, when I copy down, I get a
series of N/A errors. I think the B1:N1 have to be absolute references.

 

[Guest]

Dave,
You are correct - mea culpa! Sorry.

OK, the formula works as you have it, however, when I copy down, I get a
series of N/A errors. I think the B1:N1 have to be absolute references.