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

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!!! 😄
  - PReagan
    Bronze Contributor
    Oct 30, 2019
    Jen1206
    
    My pleasure!