Search code examples
excelif-statementexcel-formulaoffsetworksheet-function

Store an integer in a cell and use that to set the number of column gaps

I'm creating a revenue model on a simple spreadsheet. On the top, I have a variable cell (C5) that is the "number of weeks between transactions". Below that, I have a simple weekly revenue model.

Problem: What I would like is to be able to tweak the fields in yellow and have it apply dynamically to the model below so that it applies the sales amount from C4 every X weeks to the model below, where X is C5.

My initial guess was to use OFFSET, such as F10 =SUM(OFFSET(C10,0,$C$5)), but I've never used this function before and the more I think about it, I'm not sure if I'm approaching this correctly at all.

I created a mock desired results image to show what I would like to have happen if I enter 3 in C5 and then change it to 2.

Desired Results

Solution

  • May not be quite right but hopefully 'configurable' to suit. Assumes $500 is entered in C10 and (at least for the time being) 1 will not be entered in C5. Please try in D10 and copied across to suit: 

     =IF(MOD(COLUMN()-3,$C5)<MOD(COLUMN()-4,$C5),$C10,"")

    A quick fix for the above failing to populate all cells when C5 is 1 is to wrap the formula in the condition =IF($C5=1,$C10,....)

    MOD "returns the remainder after a number is divided by a divisor". The remainder being 0 when the number is an exact multiple of the divisor. If we take the first example (every third column) a suitable divisor is 3. Put =MOD(Column(),3) in A1 and copy across and the result is 1, 2, 0, 1, 2, 0, 1, ..... (Column() returns a number representing the column letter, with 1 for A, 2 for B etc., or the actual Column Heading where R1C1 reference style is checked under Working with formulas, in Formulas within Excel Options.)

    So that establishes a series where every third position is identifiable as returning a value from MOD that is less than that of the immediately preceding cell. For the second example (every second column) with a divisor of 2 =MOD(Column(),2) in A1 copied across results in 1, 0, 1, 0, 1, ... so again the reduction in the remainder signals the position required for the formula to output the requisite value.

    We are comparing the result of calculating the remainder for one column relative to the result of the same calculation performed on the previous column, hence -3, -4. Other (suitably paired) values might work equally well, it just seemed easier to 'start from zero' when in the first column for the formula (ColumnD or 4).

    (If anybody would like to provide a proper explanation please feel free to edit!)