I am trying to create a formula whereby every seventh cell vertically
subtracts 0.75 from the previous amount, for example as follows:
I3=72
I10=71.25
I17=70.5
I24=69.75
I31=69
And so on all the way down. The cells in between are empty.
How do I acheive this? Many thanks!

1. Enter 72 in I3

2. Enter the below formula in I10
=I3-0.75

3. Select I4:I10. Drag the selection handle down as required....

If this post helps click Yes
---------------
Jacob Skaria


"Josh W" wrote:

I am trying to create a formula whereby every seventh cell vertically
subtracts 0.75 from the previous amount, for example as follows:
I3=72
I10=71.25
I17=70.5
I24=69.75
I31=69
And so on all the way down. The cells in between are empty.
How do I acheive this? Many thanks!

On 18 Iul, 20:00, Josh W wrote:
And so on all the way down. The cells in between are empty.

One way :

Sub EXTRACT75()

Dim FromWbook As Worksheet
Dim myCell As Range
Dim myRng1 As Range

Set FromWbook = Workbooks("YOURWORBOOKNAMEHERE.xls").Worksheets
("YOURWORKSHEETNAMEHERE")

With FromWbook
Set myRng1 = .Range("I14:I100")
End With


For Each myCell In myRng1.Cells

If myCell.Value < Empty Then
myCell.Value = myCell.Offset(-7, 0).Value - 0.75
End If
Next myCell
End Sub

For each cell in column I which is not empty , will extract
what you wish . You'll have trouble if you don't have first
value in cell I7 and second in cell I14 , but ... that's whai I know .
Hope to help .

"Jacob Skaria" wrote in message
...
1. Enter 72 in I3
2. Enter the below formula in I10
=I3-0.75
3. Select I4:I10. Drag the selection handle down as required....

Good answer. If dragging the selection down is a drag ;), you can enter the
target range into the Name Box (normally to the right of the Formula Bar in
Excel 2003), e.g. I4:I675, then paste (e.g. press ctrl+v).


----- original message -----

"Jacob Skaria" wrote in message
...
1. Enter 72 in I3

2. Enter the below formula in I10
=I3-0.75

3. Select I4:I10. Drag the selection handle down as required....

If this post helps click Yes
---------------
Jacob Skaria


"Josh W" wrote:

I am trying to create a formula whereby every seventh cell vertically
subtracts 0.75 from the previous amount, for example as follows:
I3=72
I10=71.25
I17=70.5
I24=69.75
I31=69
And so on all the way down. The cells in between are empty.
How do I acheive this? Many thanks!

Thanks Jacob, I had huge problems finding my post. Somehow the notification
link received in my email box just opens a blank page in the communities
page, and I also don't know how to find all my posts in one place.

You solution was excellent! Simple and effective. I am embarrassed I
couldn't think of it...

"Jacob Skaria" wrote:

1. Enter 72 in I3

2. Enter the below formula in I10
=I3-0.75

3. Select I4:I10. Drag the selection handle down as required....

If this post helps click Yes
---------------
Jacob Skaria


"Josh W" wrote:

I am trying to create a formula whereby every seventh cell vertically
subtracts 0.75 from the previous amount, for example as follows:
I3=72
I10=71.25
I17=70.5
I24=69.75
I31=69
And so on all the way down. The cells in between are empty.
How do I acheive this? Many thanks!

Hi Joe, thanks for the tip. Somehow I couldn't figure it out. Could you give
me a more specific step by step guide? I know, I am real stupid...
Much appreciated.
Josh

"JoeU2004" wrote:

"Jacob Skaria" wrote in message
...
1. Enter 72 in I3
2. Enter the below formula in I10
=I3-0.75
3. Select I4:I10. Drag the selection handle down as required....

Good answer. If dragging the selection down is a drag ;), you can enter the
target range into the Name Box (normally to the right of the Formula Bar in
Excel 2003), e.g. I4:I675, then paste (e.g. press ctrl+v).


----- original message -----

"Jacob Skaria" wrote in message
...
1. Enter 72 in I3

2. Enter the below formula in I10
=I3-0.75

3. Select I4:I10. Drag the selection handle down as required....

If this post helps click Yes
---------------
Jacob Skaria


"Josh W" wrote:

I am trying to create a formula whereby every seventh cell vertically
subtracts 0.75 from the previous amount, for example as follows:
I3=72
I10=71.25
I17=70.5
I24=69.75
I31=69
And so on all the way down. The cells in between are empty.
How do I acheive this? Many thanks!

Joe "misspoke" when he said the Name Box is to the "right" of the Formula
Bar... it is locate on the *left* of the Formula Bar. Just type in an
address (for example, B2) or a range (for example, C3:M9) and that cell or
range will be selected.

--
Rick (MVP - Excel)


"Josh W" wrote in message
...
Hi Joe, thanks for the tip. Somehow I couldn't figure it out. Could you
give
me a more specific step by step guide? I know, I am real stupid...
Much appreciated.
Josh

"JoeU2004" wrote:

"Jacob Skaria" wrote in message
...
1. Enter 72 in I3
2. Enter the below formula in I10
=I3-0.75
3. Select I4:I10. Drag the selection handle down as required....

Good answer. If dragging the selection down is a drag ;), you can enter
the
target range into the Name Box (normally to the right of the Formula Bar
in
Excel 2003), e.g. I4:I675, then paste (e.g. press ctrl+v).


----- original message -----

"Jacob Skaria" wrote in message
...
1. Enter 72 in I3

2. Enter the below formula in I10
=I3-0.75

3. Select I4:I10. Drag the selection handle down as required....

If this post helps click Yes
---------------
Jacob Skaria


"Josh W" wrote:

I am trying to create a formula whereby every seventh cell vertically
subtracts 0.75 from the previous amount, for example as follows:
I3=72
I10=71.25
I17=70.5
I24=69.75
I31=69
And so on all the way down. The cells in between are empty.
How do I acheive this? Many thanks!

"Rick Rothstein" wrote:
Joe "misspoke" when he said the Name Box is to the "right" of the
Formula Bar... it is locate on the *left*

Grumble! I don't why I get left and right mixed up; started a few years
ago. It's a big joke in my family.


Just type in an address (for example, B2) or a range (for example,
C3:M9)

And press Enter.


----- original message -----

"Rick Rothstein" wrote in message
...
Joe "misspoke" when he said the Name Box is to the "right" of the Formula
Bar... it is locate on the *left* of the Formula Bar. Just type in an
address (for example, B2) or a range (for example, C3:M9) and that cell or
range will be selected.

--
Rick (MVP - Excel)


"Josh W" wrote in message
...
Hi Joe, thanks for the tip. Somehow I couldn't figure it out. Could you
give
me a more specific step by step guide? I know, I am real stupid...
Much appreciated.
Josh

"JoeU2004" wrote:

"Jacob Skaria" wrote in message
...
1. Enter 72 in I3
2. Enter the below formula in I10
=I3-0.75
3. Select I4:I10. Drag the selection handle down as required....

Good answer. If dragging the selection down is a drag ;), you can enter
the
target range into the Name Box (normally to the right of the Formula Bar
in
Excel 2003), e.g. I4:I675, then paste (e.g. press ctrl+v).


----- original message -----

"Jacob Skaria" wrote in message
...
1. Enter 72 in I3

2. Enter the below formula in I10
=I3-0.75

3. Select I4:I10. Drag the selection handle down as required....

If this post helps click Yes
---------------
Jacob Skaria


"Josh W" wrote:

I am trying to create a formula whereby every seventh cell vertically
subtracts 0.75 from the previous amount, for example as follows:
I3=72
I10=71.25
I17=70.5
I24=69.75
I31=69
And so on all the way down. The cells in between are empty.
How do I acheive this? Many thanks!