Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Set a value every 5 cells
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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200902/1 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Set a value every 5 cells
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. -- Rick (MVP - Excel) "wilchong via OfficeKB.com" <u43231@uwe wrote in message news:916fe4a10491c@uwe... 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200902/1 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Set a value every 5 cells
Dear Rick,
Thanks a lot, it works extremely well! Thanks, Wilchong Rick Rothstein wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200902/1 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Set a value every 5 cells
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 Rothstein wrote: 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 -- Message posted via http://www.officekb.com |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Set a value every 5 cells
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" <u43231@uwe wrote in message news:917075a995b20@uwe... 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 Rothstein wrote: 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 -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel: match two cells in one sheet to two cells in another and return a third cells value | Excel Worksheet Functions | |||
Cells won't change font color or show hi-lighted cells in document | Excel Discussion (Misc queries) | |||
display a range of cells editible cells based on matching date | Excel Worksheet Functions | |||
Setting of input cells as blue font and formula cells as black fon | Excel Discussion (Misc queries) | |||
trying to create an (almost) circular formula between cells and data validated cells with lists | Excel Worksheet Functions |