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. ...

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

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:

...

Then A2 could contain the formula:

=IF(MOD(COLUMN(A1),5)=0,A1+0.5,A1)

Copying this formula over would result in

1.5

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
  - Jen1206
    Copper Contributor
    Oct 30, 2019
    PReagan Thank you!!!!! You saved me hours of scratching my head here! You rock!!! 😄