If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
compare values in two different columns in a table
I have a table which lists costs associated with various projects.
The columns a 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? -- Brevity is the soul of wit. |
#2
|
|||
|
|||
compare values in two different columns in a table
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(TOD AY()),1),B1:N1,0))OFFSET(A2,0,MATCH(DATE(YEAR(TOD AY()),MONTH(TODAY()),1),B1:N1,0)-1),"OK","Check with program manager") HTH "Dave F" wrote: I have a table which lists costs associated with various projects. The columns a 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? -- Brevity is the soul of wit. |
#3
|
|||
|
|||
compare values in two different columns in a table
Thanks, looks promising.
-- Brevity is the soul of wit. "Toppers" wrote: 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(TOD AY()),1),B1:N1,0))OFFSET(A2,0,MATCH(DATE(YEAR(TOD AY()),MONTH(TODAY()),1),B1:N1,0)-1),"OK","Check with program manager") HTH "Dave F" wrote: I have a table which lists costs associated with various projects. The columns a 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? -- Brevity is the soul of wit. |
#4
|
|||
|
|||
compare values in two different columns in a table
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. -- Brevity is the soul of wit. "Toppers" wrote: 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(TOD AY()),1),B1:N1,0))OFFSET(A2,0,MATCH(DATE(YEAR(TOD AY()),MONTH(TODAY()),1),B1:N1,0)-1),"OK","Check with program manager") HTH "Dave F" wrote: I have a table which lists costs associated with various projects. The columns a 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? -- Brevity is the soul of wit. |
#5
|
|||
|
|||
compare values in two different columns in a table
Dave,
You are correct - mea culpa! Sorry. "Dave F" wrote: 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. -- Brevity is the soul of wit. "Toppers" wrote: 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(TOD AY()),1),B1:N1,0))OFFSET(A2,0,MATCH(DATE(YEAR(TOD AY()),MONTH(TODAY()),1),B1:N1,0)-1),"OK","Check with program manager") HTH "Dave F" wrote: I have a table which lists costs associated with various projects. The columns a 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? -- Brevity is the soul of wit. |
Thread Tools | |
Display Modes | |
|
|