Comparative formula

[Pizzio]

I'm trying to create a formula that gives me the $ variance between two columns. The problem i'm having is i want it to change month over month based on a date driver in Cell A1. I also need it to SUM the data in column C by sub-category. So if it's March I need my formula to sum all 'base rent' for the month of march. March would be identified in cell A1.

West WD3 budget vs actual - January 09.xls
	C	D	E	F	G	H
26	FINALSubCategory	2009.Jan	2009.Feb	2009.Mar	2009.Apr	2009.May
27	BaseRent	40,000
28		40,000	-	-	-	-
29	BuildingExpense	500,000
West

 

[Aladin Akyurek]

Yes but i have the formula on a seperate tab than the source data. Maybe that's creating an issue.

You just need to prefix the ranges with the name of the sheet where they are like in Sheet1!$A$2:$A7...

BTW, are you opting for an expensive formula?

 

[Pizzio]

I believe i found the issue. When attempting to capture the entire year the formula creates a #VALUE but if i break it down so that it just captures through September then it returns with the correct value. Is there a way to correct this?

 

[Pizzio]

One last question. I've now been ask to add in the city data. Grrr! Is there a way to add in a second driver?

 

[c_m]

I believe i found the issue. When attempting to capture the entire year the formula creates a #VALUE but if i break it down so that it just captures through September then it returns with the correct value. Is there a way to correct this?

The formula works fine for me. Make sure your edit your ranges properly.

 

[c_m]

One last question. I've now been ask to add in the city data. Grrr! Is there a way to add in a second driver?

I guess you can. It will get a bit more complicated. Also have you considered using a pivot table?

 

[Aladin Akyurek]

One last question. I've now been ask to add in the city data. Grrr! Is there a way to add in a second driver?

Do you mean something like sum for Stamps (a category) between say 1-Feb-09, first driver date and 1-Apr-09, second driver date? If so:

Control+shift+enter, not just enter:

=SUM(IF($A$4:$A$10=D$1,IF($B$3:$G$3>=B$1,IF($B$3:$G$3<=C$1,$B$4:$G$10))))

where D1 houses the category of Stamps, B1 1-Feb-09, and C1 1-Apr-09.

 

[Pizzio]

The formula worked perfectly! Thank you to all who helped with this. Who knows what my manager will come up with next. >.<