Forum Discussion

Copper Contributor

Oct 30, 2019

Solved

Need formula to increase increment value by 0.5 in every 5th cell

I am creating a workbook to show daily training sessions per XX attendees.

I need a formula to calculate the following

Users: 1, 2, 3, 4, 5, etc.

Training Session needed: 1, 1, 1, 1, 1.5, etc.

Every 5th user or column needs to increase by 0.5

Formulas and Functions

PReagan
Oct 30, 2019
Jen1206

I see. In that case, simply place this formula in Cell A1 and copy over as necessary:
=1+0.5*INT(COLUMN(A1)/5)

Let me know if this helps,
PReagan

5 Replies

PReagan
Bronze Contributor
Oct 30, 2019
Hello Jen1206,

I hope I am understanding your goal correctly.
If so, then here is one possible solution:
Assuming Row 1 contains values in adjacent cells starting in A1 as:
1 1 1 1 1 2 2 2 2 2
...
Then A2 could contain the formula:
=IF(MOD(COLUMN(A1),5)=0,A1+0.5,A1)
Copying this formula over would result in
1 1 1 1 1.5 2 2 2 2 2.5
...
- Jen1206
  Copper Contributor
  Oct 30, 2019
  PReagan Thank you for your reply!
  Your formula is very close to what I need! How can we make the following pattern happen?
  1 1 1 1 1.5 1.5 1.5 1.5 1.5 2
  - PReagan
    Bronze Contributor
    Oct 30, 2019
    Jen1206
    
    I see. In that case, simply place this formula in Cell A1 and copy over as necessary:
    =1+0.5*INT(COLUMN(A1)/5)
    
    Let me know if this helps,
    PReagan

Resources