Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

repeat a formula, skipping cells

Microsoft excel. Is it possible to quickly repeat, or copy, a formula down a
column skipping the same number of rows with each copy. e.g. give the total
of a1..a4 in b4 and copy this so that you get a5..a8 in b8, a9..a12 in b12,
etc.


Post your answer or comment

comments powered by Disqus
How to make a formula increment cell numbers every other row?

For exemple:

I have a

fixed row content
variable row content
fixed row content
variable row content

But I could have 0 rows to 1000 rows, the number is not know.

Thanks in advance,
Mark

Hi guys, does any body have a macro that would repeat a formula a set
number of rows apart (1200) in a column?There's multiple worksheets
grouped together in a workbook and it would be nice if it would repeat
in the same column same location in each. the start would be L25. The
formula is a countif that refers to data in the d column. Each
worksheet has a different end of data in the d column. Any help would
be greatly appreciated. You guys have been right on everything so far.
THANKS! steveo

Hi.

I have a value in cell A10. Then i have a formula in cell C1 which
goes: (A1+B1)/A10. If I drag the formula down to cell C5 the formula
for C2 will be (A2+B2)/A11,
for C3 will be (A3+B3)/A12,....,for C5 will be (A5+B)/A14.
I would like the formula to have the A10 parameter fixed so when i drag
the formula down the A10 would always be A10(it wouldn't go up by 1).
So the formula for C2 will be (A2+B2)/A10,
for C3 will be (A3+B3)/A10,....,for C5 will be (A5+B5)/A10. Please
help.
Thanks,

Marko

Example:

A Formula in Cell A1 should do the following:

If cell A1 equals 1, then cell B1 should have the value 5, otherwise B1
should be 6.

A condition in cell A1 is changing the value of cell B1... is that possible?

Please note that there is NO Formular in Cell B1, which is the cell to be
modified...

Thanks

Hi,

To count how much staff spend on several tasks a day, I create a spreadsheet which basically has 2 column per staff and in row are the working hours (per 30min).
There is a formula which calculate the number of minutes spend on one task. However to be able to be precise, I need to divided by two the time if there are two tasks at the same time.

The formula is something like Countif(range, type of value,...)

My problem is that I need to repeat this formula more than 12 times and for all staff. The worksheet gets too heavy. I want to include it in a macro.

Can someone explain me a little bit how to repeat a formula in a given range in a macro?

... hope that was clear enough.

cheers

LeP

Hi everyone, first post, and stumped =)

Is there any way in excel to have a formula "repeat" based off of a value in a cell? for example (the formula)

(100)*.9 [from a cell value of 1
((100*.9)+100)*9) [from a cell value of 2]
((((100*.9)+100)*9)+100)*9) [from a cell value of 3]

and so on and so forth (cell value from 1 to 20 at the moment) Any experts lend a hand? Help is greatly appreciated!

Thanks,
Chris

I would like to repeat a formula I inserted into cell A1 all the way through A2500. I know I can drag the formula but 2500 cells is a time consuming effort. Is there a quicker way to repeat the formula in all 2500 cells?

Hello, this is a cross-post and the link to the original message is here:

http://www.excelforum.com/excel-new-...-few-rows.html

I posted in the new users forum but I think it's a more appropriate question here, and also it seems like there are not many viewers in the new users forum. Admins can delete the original post; I would do it myself but I don't think there's a way to do that. I won't cross-post anymore. Here is my question:

I have a formula that is working, but I want to repeat it every seven columns. I know I can drag it and release it (or drag a few and release them in the appropriate column), but at this point I have so many columns that it's quite cumbersome to do that. Is it possible to create a macro that will automatically repeat the formula in every cell seven columns to the right (the same row for all)?

My formula is in cell K7 is:

=IF(AND(K24<=0%,K24>=-2%,OR(J32="T",J32="F")),1,"FALSE")

Now I want that formula to repeat (with the proper adjustments) for cell R7, i.e.:
=IF(AND(R24<=0%,R24>=-2%,OR(Q32="T",Q32="F")),1,"FALSE")

and then to automatically continue like that for cells Y7, AF7, AM7, and so on.

Is there a way to do this? Thank you!

Hey guys, does anyone have a formula to make a formula repeat itself in
the same column (L) the same number of rows apart and possibly jump to
the other worksheets in a workbook? This is excel for mac 2004.I'd
greatly appreciate your help. It would be nice if when data ran out in
the d column which the original formula refers to the formula simply
repeats in the same places in the other worksheets.
The cell values are relative. steveo

in cell reference H5 i have a formula H4*H3, I have copied this formula
through to DG5. In cell reference H7 I have a formula H6*H3 which i have also
copied through to DG7.I have formulas right down to cell reference H299
(H298*H3) Is there a quick way to repeat the copy bearing in mind the cell
references are not together ie H5, H7, H9 and so on.

I need to copy a formula onto a work sheet except I can't use autofill b/c there are constant breaks separated by 23 lines. It is just a simple formula of =Sheet1!A1 and it starts in cell A24 on sheet 2, the problem is when I copy the formula it jumps and skips 23 lines in the source material when I copy into cell A92 (I want this cell A92 to read 46 ect). Is there an easy way to copy the formula and have it automatically take those 23 lines taken into account?

Hi there,

I would like my macro to write a formula in cell A1 that in effect would say "=A2*x". The problem is that the value of "x" is determined earlier in this macro, and is located in another cell (we'll say A3). The formula will equal A2*A3, but I want the reference to remain for A2 and the actual value to be present for A3, so the formula will look something like "=A2*.5". I am going to copy cells A1:A2 but abandon A3 so that is why I need its value in the formula.

If I were able to make my macro copy the value from cell A3 and paste it into the formula for the other cell that would work, but I'm guessing that is not the easiest way to do this with a macro. If anyone knows how to do it I'd take it, but if anyone has a better solution I'd be very grateful for it.

Thanks,
Alex

Dear all,
I have a program on hand such that after each time I run it, I want to reset the value of the cell to zero but not delete the formula.
For eg. if I set cells(1,1)=0, the A1 cell will become 0. However, originally A1 has a formula, I run it one time such that it has a value, say, 3. I want to reset it automatically using VBA to become 0 in value but maintain the formula, how can I do this?
Cheers

I am analyzing data from a measure with two scales. There are 8 total questions in each measure. Answers from Respondent #1 will in column 1 in excel (rows a1 through a8). I have 100 respondents. Here is the dilemma, questions 1,2,3, 5 make scale #1 while questions 4,6,7,8 make scale #2.

Since I have so many respondents I would like to set up a formula where I can sum the cells that represent the respective scales (sum= a1,a2,a3,a5 / sum= a4,a6,a7,a8) but I want this apply this formula to columns 1-100 individually without having to create a new formula for each column.

Do you have any ideas how to do this? Please ask more questions if this seems unclear.

Hi,

Until now, I have been using the terrifically inefficient way of writing in a formula in a cell and copy pasting the value in my VBA code:

Easy example (concatenating):


	VB:
	
Sheets(Sheet2).Select 
Range("D1").Select 
ActiveCell.FormulaR1C1 = _ 
"=""Data collected on ""&TEXT(NOW(),""dd/mm/yyyy"")&"" at ""&TEXT(NOW(),""hh:mm"")" 
Range("D1").Select 
Selection.Copy 
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
:=False, Transpose:=False 
Application.CutCopyMode = False 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


More complex example (with IF statement):


	VB:
	
Range("D2").Select 
ActiveCell.FormulaR1C1 = _ 
"=IF(LEFT(RC[-3],13)=""No list found"",""Data requested for ""&update!R[2]C[-1]&""/""&update!R[2]C&""/""&update!R[2]C[1]& ""
out of range"",""Data downloaded for ""&update!R[2]C[-1]&""/""&update!R[2]C&""/""&update!R[2]C[1]& """")"Range("D2").Select 
Selection.Copy 
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
:=False, Transpose:=False 
Application.CutCopyMode = False 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I am sure there is a way (at least for the 1st easy example) to direct that the text result of these arguments to be written into the cell as 1 line without the copy paste operation being done, ideally directly as cells referenced as "D2", without the R1C1 referencing, which becomes confusing.

Thanks for the advice,

Helen

Can you write a formula in excel that can reference the source/or dependent cells of a specific cell?

In other words: I label my tabs 1,2,3,etc. If I link cell A1 in tab 1 to cell A1 in tab two I would like to be able to write a formula in cell B1 of tab 1 that tells me that cell A1 is linked to tab 1. For a bonus I would like to be able to write a formula in C1 that tells me any cells that are using cell A1. For example if tab 3, A1 was linked to tab 1, A1.

To put it in to formula terms:

Cell A1 of tab "1" would look like this: ='2'!A2
Then I want to write a formula in Tab "1" Cell B1 that results in: "Tab 2" or even "Tab 2 Cell A2"

Can anyone help with this?

I have a formula that will put the name of a tab in a specific cell. This is great for tables of contents and creating titles to documents or footnotes, but it is a pain staking process to use this formula for the above purpose.

That formula looks like this:

=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,34)

I am currently using 2007.

Thanks to whoever is smart enought to figure this out!

suppose in cell a1 i have gc 123 i apply a formula in cell a2 as =a1+1 but it doesnt give. so what can i do so that it returns gc 124?

I have a tab called Record. Here are the cells it contains:
A1: 0-.4
A2: .5-.9
A3: 1.0-1.4
A4: +1.5
A5: PL

This repeats in cells A6-A10,A11-A15,etc.

In another tab called Graph I have the following in cell B2 (cell B1: 0-.4)
=VLOOKUP(B1,Record!A,4,0)

The above formula works fine as it finds the first 0-.4 and gives me the
number in cell D. I would like a formula for cell B3 to find the next
occurance of 0-.4 and give me the number in the corresponding cell D.

Anyone know how to do it?

I belive it is something like this (this is another formula in my
spreadhsheet but I can't follow it). I believe I could use the same formula
but just need to reference the Record tab instead of the Scores tab, B1
instead of Capitals, and the Q and S references change to something.

=IF(ISTEXT(INDEX(Scores!$S$1:$S$50000,SMALL(IF(Sco res!$Q$1:$Q$50000="CAPITALS",ROW(INDIRECT("1:50000 "))),ROW()-1))),(INDEX(Scores!$S$1:$S$50000,SMALL(IF(Scores!$ Q$1:$Q$50000="CAPITALS",ROW(INDIRECT("1:50000"))), ROW()-1))),"")

Hi - I am trying to transfer a highlighted cell to another worksheet by use
of a formula; the cell will not always have data in it and more importantly
require just the highlight itself.

I have a formulas referencing cells in another worksheet. When rows are
added or deleted from the worksheet the formulas are either changed to skip
the new row or receive an #REF! error when a row is deleted. Regardless of
what happens in the worksheet I want the formulas to remain the same

I am analyzing data from a measure with two scales. There are 8 total
questions in each measure. Answers from Respondent #1 will in column 1
in excel (rows a1 through a8). I have 100 respondents. Here is the
dilemma, questions 1,2,3, 5 make scale #1 while questions 4,6,7,8 make
scale #2.

Since I have so many respondents I would like to set up a formula where
I can sum the cells that represent the respective scales (sum=
a1,a2,a3,a5 / sum= a4,a6,a7,a8) but I want this apply this formula to
columns 1-100 individually without having to create a new formula for
each column.

Do you have any ideas how to do this? Please ask more questions if this
seems unclear.

--
aebjeffrey
------------------------------------------------------------------------
aebjeffrey's Profile: http://www.excelforum.com/member.php...o&userid=32167
View this thread: http://www.excelforum.com/showthread...hreadid=519161

I have a formulas referencing cells in another worksheet. When rows are
added or deleted from the worksheet the formulas are either changed to skip
the new row or receive an #REF! error when a row is deleted. Regardless of
what happens in the worksheet I want the formulas to remain the same.

Hi,
I have a list of numbers in column A. I have a variable in Cell F1. I have created a formula in cell B1. I would like to drag and copy the formula in B1 down to match all the numbers in column A.

I know that I can select the outside lower right edge of the cell and holding CTRL down, drag to copy but when I do this the variable F1 gets changed to F2, F3 and so on in each of the cells of column B.
How can I make that variable stay as F1?

simple example:
A....B...................................... F
1....sum(A1 * F1).....................10
2....sum(A2 * F2)
3....sum(A3 * F3)
4....sum(A3 * F4)

all the F values in the above formulas should stay as F1.

I appreciate any help,

Bradley

What i would like to happen is after inputing a number into say column B have that number update a formula in cell D2.... so for example... lets say i have

B1 = 1000
B2 = 2000
B3 = 1400
B4 = ???
D2 = (B3-B2)/B2

is there a way to make it so when i enter a new number in B4 it will automatically update the formula to display the value for (B4-B3)/B3 and then continue on to repeat this process when i enter values into B5, B6, B7 ect?

Thanks
John


No luck finding an answer? You could always try Google.