Cell reference in Excel
 

Hello! I have asked a similar question about this but I did not get a
satisfactoty answer. I have two columns; A and B. In column A I have a list
of interest rates for different days. Since I am only interested in the
interest rate on certian days I want to single out these days and put them in
column B. I want to use every fifth interest rate that is found in column A,
e.g. I want to use only interest rates on fridays.

Thus. my question is: How to I fill column B with every fifth entry found in
A?
Here is an example of what I mean. Cell A5=2,25. Cell A10=2,45. Cell
A15=2,44. Thus, I want B1=2,23. B2=2,45 and B3=2,44.

This seems simply enough but I have not really managed. Is it possible to do
something similar to the R1C1 notation that you can use in VBA? Any help is
very much appreciated! Thank you very much!

Cell reference in Excel
 

=INDEX(A1:A50,ROW()*5)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Arne Hegefors" wrote in message
...
| Hello! I have asked a similar question about this but I did not get a
| satisfactoty answer. I have two columns; A and B. In column A I have a list
| of interest rates for different days. Since I am only interested in the
| interest rate on certian days I want to single out these days and put them in
| column B. I want to use every fifth interest rate that is found in column A,
| e.g. I want to use only interest rates on fridays.
|
| Thus. my question is: How to I fill column B with every fifth entry found in
| A?
| Here is an example of what I mean. Cell A5=2,25. Cell A10=2,45. Cell
| A15=2,44. Thus, I want B1=2,23. B2=2,45 and B3=2,44.
|
| This seems simply enough but I have not really managed. Is it possible to do
| something similar to the R1C1 notation that you can use in VBA? Any help is
| very much appreciated! Thank you very much!
|
|
|

Cell reference in Excel
 

Put in B1, and copy down:
=INDEX(A:A,ROW(A2)*5-5)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Arne Hegefors" wrote:
Hello! I have asked a similar question about this but I did not get a
satisfactoty answer. I have two columns; A and B. In column A I have a list
of interest rates for different days. Since I am only interested in the
interest rate on certian days I want to single out these days and put them in
column B. I want to use every fifth interest rate that is found in column A,
e.g. I want to use only interest rates on fridays.

Thus. my question is: How to I fill column B with every fifth entry found in
A?
Here is an example of what I mean. Cell A5=2,25. Cell A10=2,45. Cell
A15=2,44. Thus, I want B1=2,23. B2=2,45 and B3=2,44.

This seems simply enough but I have not really managed. Is it possible to do
something similar to the R1C1 notation that you can use in VBA? Any help is
very much appreciated! Thank you very much!

Cell reference in Excel
 

Thanks! But I have real trouble with this syntax when it gets a bit more
complex. There must be some way of writing so that one cell shows the rate
found in e.g. F10, the next F15, then F20 etc. It seems very easy but as I
said I have not managed..Any help on this subject is vey much appreciated!

"Max" skrev:

Put in B1, and copy down:
=INDEX(A:A,ROW(A2)*5-5)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Arne Hegefors" wrote:
Hello! I have asked a similar question about this but I did not get a
satisfactoty answer. I have two columns; A and B. In column A I have a list
of interest rates for different days. Since I am only interested in the
interest rate on certian days I want to single out these days and put them in
column B. I want to use every fifth interest rate that is found in column A,
e.g. I want to use only interest rates on fridays.

Thus. my question is: How to I fill column B with every fifth entry found in
A?
Here is an example of what I mean. Cell A5=2,25. Cell A10=2,45. Cell
A15=2,44. Thus, I want B1=2,23. B2=2,45 and B3=2,44.

This seems simply enough but I have not really managed. Is it possible to do
something similar to the R1C1 notation that you can use in VBA? Any help is
very much appreciated! Thank you very much!

Cell reference in Excel
 

Both suggestions you got do exactly what you asked for.
What is your problem?
What do you mean with "when it gets a bit more complex"?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Arne Hegefors" wrote in message
...
| Thanks! But I have real trouble with this syntax when it gets a bit more
| complex. There must be some way of writing so that one cell shows the rate
| found in e.g. F10, the next F15, then F20 etc. It seems very easy but as I
| said I have not managed..Any help on this subject is vey much appreciated!
|
| "Max" skrev:
|
| Put in B1, and copy down:
| =INDEX(A:A,ROW(A2)*5-5)
| --
| Max
| Singapore
| http://savefile.com/projects/236895
| xdemechanik
| ---
| "Arne Hegefors" wrote:
| Hello! I have asked a similar question about this but I did not get a
| satisfactoty answer. I have two columns; A and B. In column A I have a list
| of interest rates for different days. Since I am only interested in the
| interest rate on certian days I want to single out these days and put them in
| column B. I want to use every fifth interest rate that is found in column A,
| e.g. I want to use only interest rates on fridays.
|
| Thus. my question is: How to I fill column B with every fifth entry found in
| A?
| Here is an example of what I mean. Cell A5=2,25. Cell A10=2,45. Cell
| A15=2,44. Thus, I want B1=2,23. B2=2,45 and B3=2,44.
|
| This seems simply enough but I have not really managed. Is it possible to do
| something similar to the R1C1 notation that you can use in VBA? Any help is
| very much appreciated! Thank you very much!
|
|
|

Cell reference in Excel
 

Hi! Well e.g. the code you gave me where does it start i.e. what does ROW()*5
really reffer to? You see what I described was a simplification of my
problem, I have to refer to other worksheets etc. and I start at different
rows etc. I have tried the code but I have not gotten it to work so i assume
i must be doing some mistake. But in general how should you write the code
i.e. how is the syntax? I am completely worthless at excel (as you may have
noticed) and the help on the index function does not help me at all. please
help me with this! thank you very much!

"Niek Otten" skrev:

=INDEX(A1:A50,ROW()*5)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Arne Hegefors" wrote in message
...
| Hello! I have asked a similar question about this but I did not get a
| satisfactoty answer. I have two columns; A and B. In column A I have a list
| of interest rates for different days. Since I am only interested in the
| interest rate on certian days I want to single out these days and put them in
| column B. I want to use every fifth interest rate that is found in column A,
| e.g. I want to use only interest rates on fridays.
|
| Thus. my question is: How to I fill column B with every fifth entry found in
| A?
| Here is an example of what I mean. Cell A5=2,25. Cell A10=2,45. Cell
| A15=2,44. Thus, I want B1=2,23. B2=2,45 and B3=2,44.
|
| This seems simply enough but I have not really managed. Is it possible to do
| something similar to the R1C1 notation that you can use in VBA? Any help is
| very much appreciated! Thank you very much!
|
|
|

Cell reference in Excel
 

I can't speak for others, but I can't help you with a formula if you don't describe your problem exactly.
If you just require some hints; you got them; use HELP to answer your questions, like the one about ROW()

BTW is it really necessary to refer to other sheets and rows? Why not set up a summary sheet where you order the data so the
formulas are simple and fill them with the correct figures with simple =A1 (example) formulas?
--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Arne Hegefors" wrote in message
...
| Hi! Well e.g. the code you gave me where does it start i.e. what does ROW()*5
| really reffer to? You see what I described was a simplification of my
| problem, I have to refer to other worksheets etc. and I start at different
| rows etc. I have tried the code but I have not gotten it to work so i assume
| i must be doing some mistake. But in general how should you write the code
| i.e. how is the syntax? I am completely worthless at excel (as you may have
| noticed) and the help on the index function does not help me at all. please
| help me with this! thank you very much!
|
| "Niek Otten" skrev:
|
| =INDEX(A1:A50,ROW()*5)
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "Arne Hegefors" wrote in message
| ...
| | Hello! I have asked a similar question about this but I did not get a
| | satisfactoty answer. I have two columns; A and B. In column A I have a list
| | of interest rates for different days. Since I am only interested in the
| | interest rate on certian days I want to single out these days and put them in
| | column B. I want to use every fifth interest rate that is found in column A,
| | e.g. I want to use only interest rates on fridays.
| |
| | Thus. my question is: How to I fill column B with every fifth entry found in
| | A?
| | Here is an example of what I mean. Cell A5=2,25. Cell A10=2,45. Cell
| | A15=2,44. Thus, I want B1=2,23. B2=2,45 and B3=2,44.
| |
| | This seems simply enough but I have not really managed. Is it possible to do
| | something similar to the R1C1 notation that you can use in VBA? Any help is
| | very much appreciated! Thank you very much!
| |
| |
| |
|
|
|

Cell reference in Excel
 

"Arne Hegefors" wrote:
Thanks! But I have real trouble with this syntax when it gets a bit more
complex. There must be some way of writing so that one cell shows the rate
found in e.g. F10, the next F15, then F20 etc. It seems very easy but as I
said I have not managed..Any help on this subject is vey much appreciated!

First ..
=INDEX(A:A,ROW(A2)*5-5)

although the above works, it should have been the simpler:
=INDEX(A:A,ROW(A1)*5)
My error, sorry

ROW(A1)*5 is simply used as an incrementer within the INDEX formula to
generate the series: 5,10,15, etc as we copy the formula down from the start
cell, in this case, B1. You can see this happening by putting in any starting
cell, say in K2: =ROW(A1)*5 then copy K2 down. In K3 the formula will become:
=ROW(A2)*5, in K4: =ROW(A3)*5, and so on. As ROW(A1) simply returns 1,
ROW(A2) returns 2, and so on, hence we'd get the series: 5, 10, 15, etc
generated in K2 down. This numeric series is used as the INDEX's row param to
extract what's in A5, A10, A15, etc. In INDEX(A:A, ...), the "A:A" means the
index is for the entire col A, from A1 down. INDEX(A:A,1) returns whats in
A1, INDEX(A:A,5) returns what's in A5, and so on. Hope this clarifies it a
little better.

I'd normally use ROW(A1) out of "convention". ROW(B1), ROW(C1), etc all
return the same as ROW(A1), and will increment in a similar fashion, too,
when copied down. These could have been used instead as the incrementer in
this instance.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---