Search code examples
excelexcel-formulaexcel-2013

repeating a formula conditionally in excel 2013?

I am trying to calculate a formula and then dragging it to apply it for the whole column but the problem is that I want to compare first cell of the column A with the first cell of the column B and then second cell of the column A with the second cell of the column B and then compare third cell of the column a with the first cell of the column B... :

    A    B      result
1   1    4        0
2   2    5        0
3   3             0
4   4             0 
5   5             1
6   5             1

when i write the pattern like if =IF((A1<Sheet1!B1),0,1) then =IF((A2<Sheet1!B2),0,1) ,=IF((A1<Sheet1!B1),0,1),=IF((A2<Sheet1!B2),0,1) Four times and then dragging the formula for the column it start comparing it with the correspond one =IF((A5<Sheet1!B5),0,1) How should i change it ?

edit :

in the example I want to compare cell(1,A) with cell(1,B) then cell (2,A) with cell (2,B) and then cell(3,A) with cell(1,B) then cell (4,A) with cell (2,B) and then cell(5,A) with cell(1,B) then cell (6,A) with cell (2,B).[repeating the pattern two times and then start over]

Solution

  • Try this in C1,

    =--NOT(A1<OFFSET('Different Sheet'!$B$1, MOD(ROW(1:1)-1, 2), 0))

    Fill down as necessary. You only seem to be looking for a 0 or a 1 so I've simplified your IF statement. (Note: maths with MOD adjusted for more universality)

    For different multiples you should only have to change the divisor parameter of the MOD function. The ROW function used as ROW(1:1) will return 1, 2, 3, 4, 5, etc as you fill down. MOD returns the remainder of a division operation so MOD(ROW(1:1)-1, 3) filled down will return 0, 1, 2, 0, 1, 2, 0, etc.

    If you used the COUNT function on the numbers in 'Different Sheet'!B:B, you should be able to achieve a dynamic divisor.

    =--NOT(A1<OFFSET('Different Sheet'!$B$1, MOD(ROW(1:1)-1, COUNT('Different Sheet'!B:B)), 0))