Set a value every 5 cells

  • Thread starter wilchong via OfficeKB.com
  • Start date
W

wilchong via OfficeKB.com

There is 1 data set is assumed running in A1 down, viz.:
In A1 down is: 1, 2, 3, 4, 5, 6, 7, 8, 9 and 10
In the cell from B1 to B10 is empty (no value).

My question is that I want a excel formule would performance a task which it
can put a value e.g.1 every "5" cells. So, in my example, after setting the
formula, "1" will incorporate in the cell B5 and B10. Do you think an Excel
function can achieve the result according my requirement?

Many thanks, Wilchong
 
R

Rick Rothstein

You should always ask the question you actually want an answer to rather
than asking a simplified question (your e.g. suggests this is not the
question you really want an answer to)... the odds are the solution to the
simpler problem will not be able to easily be modified to handle the real
question. Anyway, here is a formula to do what you asked...

=IF(MOD(ROW(),5)=0,"1","")

Put it in B1 and copy it down.
 
W

wilchong via OfficeKB.com

Dear Rick,
Thanks a lot, it works extremely well!

Thanks,
Wilchong

Rick said:
You should always ask the question you actually want an answer to rather
than asking a simplified question (your e.g. suggests this is not the
question you really want an answer to)... the odds are the solution to the
simpler problem will not be able to easily be modified to handle the real
question. Anyway, here is a formula to do what you asked...

=IF(MOD(ROW(),5)=0,"1","")

Put it in B1 and copy it down.
There is 1 data set is assumed running in A1 down, viz.:
In A1 down is: 1, 2, 3, 4, 5, 6, 7, 8, 9 and 10
[quoted text clipped - 9 lines]
Many thanks, Wilchong
 
W

wilchong via OfficeKB.com

Dear Rick,
Your suggested formula working very well. However, when I move the B1 to B2,
B2 to B3,……etc. The result is different. The value is not located in the
five, it is located in the first 4th cell of B5, however, my correspondence
colume in colume A shows 4.

Do you think I can put a range e.g. column A2:A11 in the function ROW()?

Many thanks,
Wilchong



Rick said:
You should always ask the question you actually want an answer to rather
than asking a simplified question (your e.g. suggests this is not the
question you really want an answer to)... the odds are the solution to the
simpler problem will not be able to easily be modified to handle the real
question. Anyway, here is a formula to do what you asked...

=IF(MOD(ROW(),5)=0,"1","")

Put it in B1 and copy it down.
There is 1 data set is assumed running in A1 down, viz.:
In A1 down is: 1, 2, 3, 4, 5, 6, 7, 8, 9 and 10
[quoted text clipped - 9 lines]
Many thanks, Wilchong
 
R

Rick Rothstein

If I understand your question correctly, try this formula...

=IF(MOD(ROW(A1),5)=0,"1","")

where I have put in the A1 (use that no matter what row you put the formula
in) inside the ROW function call.

--
Rick (MVP - Excel)


wilchong via OfficeKB.com said:
Dear Rick,
Your suggested formula working very well. However, when I move the B1 to
B2,
B2 to B3,……etc. The result is different. The value is not located in the
five, it is located in the first 4th cell of B5, however, my
correspondence
colume in colume A shows 4.

Do you think I can put a range e.g. column A2:A11 in the function ROW()?

Many thanks,
Wilchong



Rick said:
You should always ask the question you actually want an answer to rather
than asking a simplified question (your e.g. suggests this is not the
question you really want an answer to)... the odds are the solution to the
simpler problem will not be able to easily be modified to handle the real
question. Anyway, here is a formula to do what you asked...

=IF(MOD(ROW(),5)=0,"1","")

Put it in B1 and copy it down.
There is 1 data set is assumed running in A1 down, viz.:
In A1 down is: 1, 2, 3, 4, 5, 6, 7, 8, 9 and 10
[quoted text clipped - 9 lines]
Many thanks, Wilchong
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top