|
Help with formula
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! |
Help with formula
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! |
Help with formula
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 . |
Help with formula
"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! |
Help with formula
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! |
Help with formula
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! |
Help with formula
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! |
Help with formula
"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! |
| All times are GMT +1. The time now is 02:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com