#### 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.aspx/excel-new/200902/1

```
 0
wilchong
2/9/2009 7:00:30 AM
excel.newusers 15348 articles. 2 followers.

4 Replies
555 Views

Similar Articles

[PageSpeed] 12

```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.aspx/excel-new/200902/1
>

```
 0
2/9/2009 7:37:53 AM
```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.aspx/excel-new/200902/1

```
 0
wilchong
2/9/2009 7:52:52 AM
```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

```
 0
wilchong
2/9/2009 8:05:22 AM
```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
>

```
 0
2/9/2009 8:12:12 AM

Similar Artilces:

Prevent clicking on a cell
I want to run the code below to prevent a range of cells from being selected if the Range("Q7") = 1. I have all cells on the worksheet locked but the user must be able to click on the locked cells to trigger a userform so I have to check Select Locked Cells. So is there any way make the Range("B5:C5") unselectable? If Range("Q7") = 1 Then Range("B5:C5").Locked = True End If Hi, >So is there any way make the > Range("B5:C5") unselectable? No but you can stop them staying there. Private Sub Worksheet_...

Drop-down list #5
I created a drop-down list in a separate sheet in my workbook. I named it Vehicles. Now I want to add to the list, but I can't figure out how to do it. I know ig must be so easy, but I'm stumped. Please help. If it's a one-time occurrence, you can press Ctrl+F3, click on the name, and extend the formula. Otherwise, I would make the range dynamic. You can learn how to do that here: http://www.contextures.com/xlNames01.html#Dynamic HTH Jason Atlanta, GA >-----Original Message----- >I created a drop-down list in a separate sheet in my workbook. I named it >Vehicles...

if cell is text move left one column
ColB is a long list with sections names followed by category codes I need to move the text into colA leaving colB with codes only (all numbers) ColB. Doors 940590 555998 447006 447008 810697 810705 810706 810707 Windows 619435 525691 525692 Try Sub Macro1() Dim lngRow As Long For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row If Not IsNumeric(Range("B" & lngRow)) Then Range("A" & lngRow).Value = Range("B" & lngRow).Text Range("B" & lngRow).Value = "" End If Next End Sub -- Jacob ...

How do I set up a daily average of unit sales formula
More info required. -- HTH RP (remove nothere from the email address if mailing direct) "jim m" <jim m@discussions.microsoft.com> wrote in message news:7E6D4510-97C1-42D4-A402-5590201C6065@microsoft.com... > ...

Can I copy radio buttons so that the second set is independent of.
I have a group of four radio buttons that all point to one cell. My goal is to create a second set that looks identical to the first but which points to a second cell. This is for a questionaire that will have many questions with the same four answer values. I want to be able to quickly create 100 button groups that will each update a separate cell for scoring of the questionaire. There are optionbuttons on the Control toolbox toolbar and there are optionbuttons on the Forms toolbar. Each has different behaviors. If I had to use lots, I'd use the Forms version. Here's a pos...

rounding up values
Has anyone done round up of values to the nearest dollar.For example I want to give a 10% of the price to my customers but if the result is other than .00 then I wanted to round up to the nearest dollar amount.My calculation using sql has been price * percent and then subtract the value from the price, then what do I need to do to roundit up??Thanks for your suggestion.Also I have a problem with my customers that I am extracting and the query does return all the values from 2004 and 2006 that are equal except for the price I have given them, how do I get only the latest ones in 2006 and not th...

TempVars unusable in field default value
Hello, I'm trying to use a temporary variable to keep track of which CSR is inputting data. I have a macro which prompts user for ID code, which is stored in the temp variable TempUser. On a form control default value property, I can use the expression [TempVars]![TempUser], which will populate that user's ID code into the control. However, I cannot use that same expression in the tables field default value property. If I try, when I save the changes to the table, I get the error message "Could not find the field 'TempVars]![TempUser'. " Any ideas why I ca...

fiscal year setting question
hi. i know that the fiscal year settings get more or less set in stone, so i had a quick question before i set it for my org. in the template field, what is that used for? if i select 'quarterly', does that mean i can only run reports on a quarter basis? what if i wanted to do monthly reports? ultimately, my question is what is the 'template' field used for? thanks! Hi Jeff, Fiscal year setting must be aligned to your company's accountig policy. Fiscal year setting primarily defines sales quotas. When you set quarterly periods, you can set quarterly quotas for mem...

Offline Folders #5
I am trying to get an outlook client setup so that it automatically opens offline without prompting the user. Does anyone know if this is possible, if so how to do it. Thanks Phil pearson In the properties of the e-mail account (Tools, Options, Mail Setup, E-mail Accounts, View or Change existing e- mail accounts) if this is an Exchange e-mail account, highlight it and hit Change, then More Settings. Select Manually Control Connection State, then select Work Offline and Use Dialup Networking. This should give you the desired effect. >-----Original Message----- >I am trying ...

How do I extend a underline across an entire cell?
When working on a financial statement, I was curious how to 1. Have a line extend across an entire cell even if the number is only 2-3 digits and 2. How to apply a double line under a number without using the = sign in the following cell? Hi Lindsay Look on the formatting toolbar for Borders -- Regards Ron de Bruin http://www.rondebruin.nl "Lindsay" <Lindsay@discussions.microsoft.com> wrote in message news:F4C9ED6C-7F2D-4277-86CC-6FA46D315DA5@microsoft.com... > When working on a financial statement, I was curious how to 1. Have a line > extend across an entire ce...

Separating Date and Time in a cell
I have a column of cells in the format "11/01/02 06:21". I would like to separate the text into 2 cells - one with the date and the other with the time. My attempts with LEFT and RIGHT have been unsuccesful. Thanks for your help Sameer --- Message posted from http://www.ExcelForum.com/ For the date use =INT(A1) replace A1 with the first cell of your range for time =MOD(A1,1) you probably have to reformat the first to mm/dd/yy (or whatever the setting is) and hh:mm Note that you can do this by just using format but if you want to compare to other cells with just pure d...

Outlook set up
In MS Outlook 2002: When I send an email, it has my email address in the from field. People receiving it cant tell it is from me, and it gets deleted. How can I make MY NAME appear in that from field instead of my email address? On Mon, 19 Jan 2004 11:17:36 -0800, "etata" <anonymous@discussions.microsoft.com> wrote: >In MS Outlook 2002: When I send an email, it has my email >address in the from field. People receiving it cant tell >it is from me, and it gets deleted. How can I make MY >NAME appear in that from field instead of my email address? Just a...

cell contents revert to 0 when i click on the next cell
I put a number into a cell click on the next cell and the first cell reverts to 0. If I format to number with 2 decimal places it will be ok but when I try to take out decimal places it goes back to zero, Help please You haven't said what number you are trying to put into the cell, but I suspect that the number is less than 0.5. A quick test shows that if you set the cell to no decimal places then enter a number less than 0.5 it is displayed 'rounded down' so it will show as zero, if it's 0.5 or above it displays as 1. If you need to put numbers less than 0.5 into youe c...

Moving Exchange #5
I am needing to move my Exchange server off of the SBS box that it is currently on and move it to another, new server. I cannot keep the servers the same name as I need the old server to continue to run SQL. Are there white papers on how to do this? TIA Scott T. On Fri, 18 Aug 2006 08:43:40 -0700, scottdog129 <scottdog129@discussions.microsoft.com> wrote: >I am needing to move my Exchange server off of the SBS box that it is >currently on and move it to another, new server. I cannot keep the servers >the same name as I need the old server to continue to run SQL. Are...

request to solve my problem #5
hello friends this is vijay,i got a problem while doing my project based on vc++ (i.e)"How to implement Fit Window feature in VC++6.0 in an MDI Application" if any of you got any idea about that please do reply me with the proceedure.i will be waiting for your reply bye with regards vijayprasadreddy ...

Accommodating for empty cells in this formula?
I have a formula in cell H21, for example, reads like this: =IF(\$G21<>"",(\$H20-\$G21),"") is there a way to adjust the formula so that an empty cell in G21 doesn't give the #VALUE! in subsequent cells in column H? Just to give a similar example, this formula =SUMIF(A1:A9,"<>0") adjusts for any and all empty cells in A2 to A9. It no longer matters if any of the cells are empty, the formula correctly gives the correct addition of A1 plust a sum of everything between A2 to A10 without any #VALUE! results. Was hoping to have the formula above als...

140 MB file went to 5.08 MB after editting 1 table
Hello All - I need some ACCESS insight...please... Several years ago, I built an access db to track my business scheduling and accounts payable/receivable. So this database is EXTREMELY IMPORTANT TO ME. The file has grown to 140 MB. Today I made a copy of the file and then edited my calendar table. I removed all columns which had 2006 data (72 totals columns) - the table had about 144 columns originally. I then added 72 columns with 2008 headers. These columns are now blank since I have not added any 2008 data yet. Afterwards, I looked around and everything looks good - my 2007 data is the...

set print area #2
Where is the "set print area" button in Excel 2002 that was in Excel '97? =?Utf-8?B?Q2Fyb2wgTWM=?= wrote > Where is the "set print area" button in Excel 2002 that was in Excel '97? > Are you sure it was "standard"? Only way I could get in was to right-click the toolbar|Customise and drag it to the toolbar from Commands|File -- David ...

How do I set up a 98% baseline across my chart?
I am trying to show on a bar chart the baseline that represents SLA requirement of system up time of 98%. How do I do this? Hi, You can find information on adding a datum line here. http://peltiertech.com/Excel/Charts/AddLine.html Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Deb" <Deb@discussions.microsoft.com> wrote in message news:11C7073F-1AC3-4FFD-BE4B-88605C7E0EF8@microsoft.com... >I am trying to show on a bar chart the baseline that represents SLA > requirement of system up time of 98%. How do I do this? ...

line chart with NA() values
12 month line chart, with some values being 0. I am using an if statement that turns any 0 values to #N/A so they do not show on the graph (which is what I want). My problem arises when the 0 values fall in the middle of my data. So for example: 1) data for all months (Jan-Dec), the line shows across all 12 months; 2) I have data for only 6 months (Jul-Dec), the line starts in Jul and ends in Dec (perfect); 3) When I have data from Jan-Mar, and Oct-Dec, the line connects between Mar and Oct. I want 2 distinct lines with no line where there is no data (#N/A). Any suggestions? -- gri...

Automatic changes in cells
Hi for some reason I now have to save my work for any formlas etc to change when I update a worsheet, how can I stop this as it is a pain and sometimes I need to do changes to see how they work before saving the work. Many thanks Click on Tools | Options | Calculation tab and set to Automatic calculation, as it is probably set to Manual. You can press F9 to force a recalculation under a manual setting. Make sure you save the file with the Automatic setting, to avoid it happening next time. Hope this helps. Pete On Feb 1, 11:42=A0am, Office 2004 Test Drive User <heepenm...@yahoo.co.u...

Multivalue with Null value SSRS 2005
I have a query to populate a multivalue parameter: SELECT distinct cast(AGRPYear.value as varchar(4)) + AGRPMonth.value 'ReportDate' FROM TPROJECT AS TPROJECT One of the values that is returned from this query is NULL. However, when I run the report, the NULL value does not show in the dropdown. I've also tried adding "select NULL as 'ReportDate' union" to the above query and the null value still doesn't show. As a result some of the records in my database have a null value for this field, they will never show up on my report. Any id...

Run time error 5 : HQClient
When I double click HQ Client I have message box "Run time error 5 : Invalid procedure call or argument" how should I do? I used RMS 2.0 -- TOY2TOY ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGr...

cell colour change when set markers are reached
i need to get a cell to change colour when markers are reached eg a qualification lasts 12 months. what i want to do is have the cell change from yellow to orange to red as the expiry date gets closer. If column A contains expiry dates then select column A, Formats>Conditional Formatting>formula1: =DATEDIF(TODAY(),A1,"m")<1 red for 1 month Click Add button, formula2: =DATEDIF(TODAY(),A1,"m")<2 orange for 2 month Click Add button, formula3: =DATEDIF(TODAY(),A1,"m")<3 yellow for 3 month Adjust number of months as you like! Regards,...

Saving #5
Is there a way of speeding up saves in excel or a way to condense the size of a workbook. Jason, This link can help with the size issue... http://www.contextures.on.ca/xlfaqApp.html#Unused Item 2d is the one you want. Regards, Jim Cone San Francisco, USA "Jason Zischke" <JasonZischke@discussions.microsoft.com> wrote in message news:18CCEA1F-2479-4ED9-A5F6-21B781D0F403@microsoft.com... > Is there a way of speeding up saves in excel or a way to condense the size of > a workbook. ...