How to extend group of 3 formula down a column?

[arctan27]

Hi everyone,


I'm have a table of outputs with IF functions. Basically I want to type a number in one column cell (Column A), which determines the output of the output cell in the next column (Column B) using the IF function. My issue arises because these output cells are in groups of 3, so for example, 3 consecutive cells in column B all refer to and depend on the input of the single one Column A cell. If I input 1 for example, I expect all 3 cells in column B to change to correspond to that input. If I change the input to 2, all 3 cells in column B change as well.


I want to extend this entire group of 3 formulas down the entire table (which is naturally very large). I've tried doing 3 or 4 groups of formulas, and extending them down, but the problem I have is Excel does not keep the reference cells in the IF formulas in column B consistent. For example, I want the formulas for cells B1 to B3 to refer to A1, B4 to B6 to refer to A2, B7 to B9 to refer to A3 and so on and so forth. But when I copy it the reference cells in column B formulas skips, and becomes for example B7 to B9 refers to A20 instead of A3.


Does anyone know a solution to this conundrum?


Cheers.

 

[Derek73]

B1=OFFSET($A$1,(ROUNDUP(ROW(A1)/3,0)-1),0)

Copied down, that mathematically manipulates the reference so B1-B3 refers to A1, B4-B6 refers to A2, etc.