Get data from a cell N columns to the left or right

 

Tools: functions OFFSET, IF

Level: advanced

Problem

You plan a marketing campaign that will run for three months. Your boss estimates that the campaign will generate 2 dollars in revenue for every dollar spent in each of the next three months thereafter. In other words, each 100 dollars spent during month 1 will result in 200 dollars during month 2, 200 during month 3 and 200 during month 4. Tomorrow, she has a meeting with her boss. She warned you that she and her boss agreed that campaign effect will last for three months, but the boss might have different assumptions for expense-to-revenue ratio and for the lag separating expenses and revenue for each of the three months. You are instructed to create an Excel model where she could change the ratios and lags for each month.

 

You assume that you will need something like this table below. You think that the model should also be extensible. What if they will decide in the meeting to extend the campaign by three more months? In this case, the model will need to cover 9 months. This means you will need to place in cells D5:I7 a single one-size-fit-all formula.

 

  A B C D E F G H I
    Month
    Marketing expense      
                 
Effect # Ratio Lag, months            
Total    

 

Question is, is such a formula possible at all?

Solution

Yes. Such a formula is possible in more than one way. We will use the simplest one. First, we need to create a method for formula to automatically take the marketing expense from the period N months BEFORE. And second, we must make sure that under no circumstances the formula will try to go further left than month 1 – that is, back to the past where there is no marketing expense, just text labels.

  1. Active cell D5.
  2. Type or insert =IF(.
  3. Enter D$1>$C5 as "logical_test". This will check if the current month is greater than the lag.
  4. Type of insert OFFSET function as "value_if_true". We will need it for referring to a month specified by lag.
  5. Enter D$2 as "reference". This is the starting point, i.e. the marketing expense for this month.
  6. Enter 0 as "rows". You instruct the OFFSET function to stay in the same row as the reference.
  7. Enter –C$5 as "cols" and close the bracket. You instruct the OFFSET function go as many columns to the left as the lag reference. OFFSET function found the marketing budget that affects this month.
  8. Type *$B5 to compute revenue by multiplying expense by ratio.
  9. Return to function IF. Enter 0 as "value_if_false" and press Enter or click OK.
  10. Copy cell D5 to cells D5:I17.

 

Your completed formula will looks as follows:

 

=IF(D$1>$C5,OFFSET(D$2,0,-$C5)*$B5,0)

Formula syntax

Code Variable Comment
=IF(   Prevent addressing to earlier periods that are outside the model
D$1>$C5, Logical_test Check if current period is greater than the lag
OFFSET( Value_if_true If yes, find marketing budget that generates revenue in this period
D$2, Reference Start from current period marketing budget cell
0, Rows Stay in the same row
-$C5 Cols Go left as many column as there are months in the lag
)   Function OFFSET complete
*$B5,   Multiply marketing budget by ratio factor to get revenue
Value_if_false If current period is equal to or smaller than the lag, there is no marketing budget for it – set it to zero.
)   Function IF complete

 

A drop-down list of values

 

Tools: Data Validation

Level: advanced

Problem

You want to make a dashboard style interface where you would choose variables from a list, rather than re-typing them.

Solution

Data Validation for a cell where your variable is allows to create nearly exactly what you want. The drop-down list will be visible only when the cell is active but it will be a list.

  1. On a new service sheet, create a list of items that will be included in your drop-down list.
  2. Name this list "dropdown_list".
  3. Active cell where the variable will be.
  4. Menu command Data-Validation or ribbon tab Data - button Data Validation.
  5. In dialog, select Allow-List.
  6. In a range field that appears, insert name "dropdown_list".
  7. (OPTIONAL) Create Input and Error Alert messages in subsequent dialog tabs if you want to. They might be useful if you give your Excel file to someone else to play with.
  8. Press Enter or Click OK.

 

You now have in active cell a drop-down list with all values of "dropdown_list" listed.

Similar tasks

Things to remember about lists:

  • They are only visible when the cell is active.
  • They cannot have labels that appear in the list instead of values they provide. There are some workarounds for that, though.
  • If a worksheet is not protected and validated cell is locked, your validated cell can be overwritten by just copying and pasting something over it. Watch out.