Dont know the right formula to use

[excellrookie]

Hey, thanks for your help in advance.

I need some help solving this problem. I am trying to create a formula that would answer the following question:
-For the month of X, whats is the ROI for marketing bucket Z
-To put it more concretely: For the 3d month of 2011, what is the ROI for marketing bucket SEM

The process for solving this would be as follows:
1) In a cell I enter the following value "SEM"
2) In a different cell, I enter the following value "1/2011"
3) Excel takes the value SEM, goes to spreadsheet ZTM, searches for all rows that are labeled SEM Revenue and SEM Cost
4) Then, Excel would calculate (REVENUE - COST) / COST, but the values it would use for the calculation are the values that are located under the column for the month of January, 2011. So that if I needed to calculate ROI for SEM for 2/2011, I could simply just change the data in step 1 and step 2.

I hope I am being clear in my explanation….

I was going in the direction of a SUMIF formula with an OFFSET function embedded in it to limit it to only the months needed for the calculation, but I found out that SUMIF was not the right formula to use and now I am stuck.

 

[GlennUK]

Where in your process is the reference to marketing bucket Z? You mention that you want the calculation to be for that bucket, but no explanation of what that is.

 

[Mike Blackman]

Hi,

Maybe something like this, if you have blanks in there let me know and i'll change it as you may get errors.

=SUMPRODUCT(--(A1:A20="SEM"),--(B1:B20="1/2011"),(C1:C20-D1:D20)/D1:D20)

Column A = SEM Buckets
B = Period
C = Revenue
D = Cost

 

[excellrookie]

Where in your process is the reference to marketing bucket Z? You mention that you want the calculation to be for that bucket, but no explanation of what that is.

The "Z" for the marketing bucket is a place holder to where you would put a name for a marketing bucket. So Z could equal to SEM bucket (Search Engine Marketing) or any other name.

 

[excellrookie]

Hi Mike,

Thanks for all your help. The formula you developed will not work for this problem because the periods are not in different rows but rather in columns.

I am attaching a link to the picture (photobucket) of the spreadsheet.

Also, dissecting your formula further (so i can learn how to build such formulas)

=SUMPRODUCT(--(A1:A20="SEM"),--(B1:B20="1/2011"),(C1:C20-D1:D20)/D1:D20)

The -- does what operation?

 

[GlennUK]

Maybe something like:
=(SUMIF($B$1:$B$34,Z_Bucket&" Revenue",OFFSET($D$1,0,MATCH(Req_Date,$E$1:$K$1,0),34,1))-SUMIF($B$1:$B$34,Z_Bucket&" Cost",OFFSET($D$1,0,MATCH(Req_Date,$E$1:$K$1,0),34,1)))/SUMIF($B$1:$B$34,Z_Bucket&" Cost",OFFSET($D$1,0,MATCH(Req_Date,$E$1:$K$1,0),34,1))

... changing all instances of 34 to the last row number of the range for processing.

 

[excellrookie]

Maybe something like:
=(SUMIF($B$1:$B$34,Z_Bucket&" Revenue",OFFSET($D$1,0,MATCH(Req_Date,$E$1:$K$1,0),34,1))-SUMIF($B$1:$B$34,Z_Bucket&" Cost",OFFSET($D$1,0,MATCH(Req_Date,$E$1:$K$1,0),34,1)))/SUMIF($B$1:$B$34,Z_Bucket&" Cost",OFFSET($D$1,0,MATCH(Req_Date,$E$1:$K$1,0),34,1))

... changing all instances of 34 to the last row number of the range for processing.

I am going to try that first thing tomorrow morning (Im in Cali). Thanks Glenn!

P.S. the "Z_Bucket&", how would i have that populated by what i input in a different cell. Meaning how do i have it be replaced by, for example, SEM.

 

[GlennUK]

The SEM you would type into a cell named Z_Bucket, or replace Z_Bucket with the cell reference where the SEM is stored.