+ Reply to Thread
Results 1 to 7 of 7

understanding MOD function

  1. 10-07-2013, 06:51 PM #1
    H28Sailor
    H28Sailor is offline
    Forum Contributor
    Join Date
    07-09-2013
    Location
    Dunedin, New Zealand
    MS-Off Ver
    Excel 2016
    Posts
    105

    understanding MOD function

    I have the following data
    ***********************
    Sheet 1
    4/10/2013 1 97.254 97.401 97.113 97.132 6454.07
    4/10/2013 2 97.131 97.25 97.03 97.215 5501.74
    4/10/2013 3 97.216 97.3 97.097 97.221 4413.97
    4/10/2013 4 97.222 97.309 97.122 97.277 3802.76
    4/10/2013 5 97.276 97.294 97.142 97.262 3534.71
    4/10/2013 6 97.262 97.267 97.05 97.101 3788.87
    4/10/2013 7 97.101 97.173 96.952 97.117 6334.11
    4/10/2013 8 97.118 97.169 96.985 97.08 5989.99
    4/10/2013 9 97.083 97.238 97.073 97.174 4283.57
    4/10/2013 10 97.173 97.201 97.08 97.104 3565.61
    4/10/2013 11 97.105 97.195 97.086 97.191 3476.76
    4/10/2013 12 97.191 97.236 97.156 97.194 2709.11
    4/10/2013 13 97.194 97.206 97.072 97.159 4298.25
    4/10/2013 14 97.159 97.167 96.985 97.101 6580.02
    4/10/2013 15 97.107 97.282 97.1 97.125 6683.83
    4/10/2013 16 97.126 97.216 97.037 97.205 5126.46
    4/10/2013 17 97.204 97.454 97.163 97.355 5219.53
    4/10/2013 18 97.353 97.48 97.315 97.397 2885.45
    ****************************************************
    In sheet 2 I am seeking the following data
    4/10/2013 6 97.254 97.309 97.03 97.101 27496.12
    4/10/2013 12 97.101 97.238 97.952 97.194 26359.15
    4/10/2013 18 97.194 97.48 97.037 97.397 30793.54
    where column 3 is the value from 6th previous row in sheet 1
    column 4 is the maximum from previous 6 rows in sheet 1
    column 5 is the minimum from 6 previous rows in sheet 1
    column 6 is value from row in sheet 1 with same date and time value
    column 7 is sum of 6 previous rows in sheet 1
    ***********************************
    Possible formulae
    column 3 formula could be =INDEX(Sheet1!C:C,ROWS($A$1:$A6950)*6 - IF(MOD(COLUMN(C$1),6=3, 10, 6))
    note 6950 is just the current row number
    column 6 formula could be =INDEX(Sheet1!C:C,ROWS($A$1:$A6950)*6 - IF(MOD(COLUMN(C$1),6=3, 9, 5))
    Both of these seem to give me what I want

    I would appreciate any pointers for similar formulae to generate columns 4, 5 and 7 results

    Many thanks

    Bob M
    Reply With QuoteRegister To Reply
  2. 10-07-2013, 07:06 PM #2
    alansidman
    alansidman is online now
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,896

    Re: understanding MOD function

    Attach a sample workbook. It is difficult to make out where a column starts and ends in your presentation. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
    Reply With QuoteRegister To Reply
  3. 10-07-2013, 07:33 PM #3
    H28Sailor
    H28Sailor is offline
    Forum Contributor
    Join Date
    07-09-2013
    Location
    Dunedin, New Zealand
    MS-Off Ver
    Excel 2016
    Posts
    105

    Re: understanding MOD function

    Here we go..............

    Bob M
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 10-07-2013, 07:59 PM #4
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: understanding MOD function

    Here's another offering using =OFFSET()
    Attached Files Attached Files
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
  5. 10-07-2013, 08:52 PM #5
    H28Sailor
    H28Sailor is offline
    Forum Contributor
    Join Date
    07-09-2013
    Location
    Dunedin, New Zealand
    MS-Off Ver
    Excel 2016
    Posts
    105

    Re: understanding MOD function

    Hi Richard

    Thank you - that is brilliant and exactly what I want

    What do I need to change to have sheet 2 data in columns A thru G rather than columns I thru O ? (as in your example excel workbook)

    Bob M
    Reply With QuoteRegister To Reply
  6. 10-07-2013, 11:25 PM #6
    H28Sailor
    H28Sailor is offline
    Forum Contributor
    Join Date
    07-09-2013
    Location
    Dunedin, New Zealand
    MS-Off Ver
    Excel 2016
    Posts
    105

    Re: understanding MOD function

    Sorry.........
    problem with formatting cells

    All good

    Bob M
    Reply With QuoteRegister To Reply
  7. 10-08-2013, 08:40 AM #7
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: understanding MOD function

    Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

    In future, to mark your thread as Solved, you can do the following -
    Select Thread Tools-> Mark thread as Solved.

    Incase your issue is not solved, you can undo it as follows -
    Select Thread Tools-> Mark thread as Unsolved.

    Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. help understanding a function
    By ds0919 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-03-2013, 04:13 PM
  2. Need Help using and understanding IF function
    By isixes27ce in forum Excel General
    Replies: 5
    Last Post: 11-09-2012, 04:16 PM
  3. Understanding an IF Function
    By rcrhymes123 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-13-2011, 01:31 PM
  4. Understanding the Sumproduct function
    By Blake 7 in forum Excel General
    Replies: 2
    Last Post: 01-31-2011, 12:11 PM
  5. understanding IF function
    By beets in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 03-18-2008, 11:29 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1