+ Reply to Thread
Results 1 to 10 of 10

Counting Spaces Between Entries

  1. 03-22-2017, 04:00 PM #1
    RachelMads02
    RachelMads02 is offline
    Forum Contributor
    Join Date
    02-27-2015
    Location
    Los Angeles, California
    MS-Off Ver
    office 2010 pro
    Posts
    238

    Counting Spaces Between Entries

    Hi guys, couple questions about counting the space between entries:

    Starting in Cell A4 is the date "1985". The column will eventually be expanding until cell A1000.

    Starting in Cell B4 are the entries. The column will eventually be expanding until cell B1000. As per the document attached there are entries in cells B10, B12, B16, B20, B31, B32, B35. In this column entries will be empty or have entries of 1, 2, 3, or 4.

    Example: The space between B4: B10 is 6, B10 & B12 is 1, The space between B12 to B16 is 3, The space between B16 to B20 is again 3, From B20 to B31 is 10, B31 to B32 is 0, and finally B32 to B35 is 2.


    Question #1:

    What formula can i use to get the average blank spaces between entries? The Total amount of blank spaces would be (6+1+3+3+10+0+2) = 25. 25/7 = 3.57.

    Question #2:
    What formula can i use to get the average spaces between specific entries such ones, twos, threes, and fours? There's 6 spaces from 1985 until the first #1. Then 1 space until the second #1. Three spaces until the 3rd #1, and another three spaces until the 4th #1, then eleven spaces until the 5th #1, and finally another three spaces until the 6th #1.

    Thanks in advance!!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-22-2017, 04:22 PM #2
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Counting Spaces Between Entries

    For the first part try
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Still working on the last part.
    Dave
    Reply With QuoteRegister To Reply
  3. 03-22-2017, 04:31 PM #3
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Counting Spaces Between Entries

    For the second part try
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 03-22-2017, 10:38 PM #4
    RachelMads02
    RachelMads02 is offline
    Forum Contributor
    Join Date
    02-27-2015
    Location
    Los Angeles, California
    MS-Off Ver
    office 2010 pro
    Posts
    238

    Re: Counting Spaces Between Entries

    I thought the average was 3.57. Your first answer gives 3.71. Is my math wrong?

    I thought the average between an entry of 1 is 4.33. From 1985 until the first "1" entry in 1991 = 6 spaces. From 1991-1993 = 1 space. From 1993-1997 = 3 spaces and 1997-2001 = 3 spaces. From 2001-2013 = 11 spaces and from 2013-2016 = 2 spaces. 6+1+3+3+11+2 = 26. 26 divided by 6 = 4.33. Your 2nd answer provides 4.5. Again is my math wrong?

    How can i use a formula to support all the way up to cell B1000? I need the range to go from B4:B1000, but it can't affect the average until an entry is inserted. So the current average would take into consideration all "1" entries from 1985-2017, but if an entry is inserted for 2018 (next year) then the average will change; rinse wash repeat.

    I need a formula to calculate this average based on "1" entered into column B. Then the same formula for "2", and "3", and finally "4".
    Last edited by RachelMads02; 03-22-2017 at 10:47 PM.
    Reply With QuoteRegister To Reply
  5. 03-23-2017, 02:04 AM #5
    avk
    avk is offline
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,223

    Re: Counting Spaces Between Entries

    Try with
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Look in "B" column : Data from "B4" to "B36" Total count is 33
    In "B" column total entries is 7 & total blank cell is 26
    it mean : (Total Blank / Total Entires = Average)
    26/7 = 3.714285714

    atul

    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
    Reply With QuoteRegister To Reply
  6. 03-23-2017, 02:51 AM #6
    RachelMads02
    RachelMads02 is offline
    Forum Contributor
    Join Date
    02-27-2015
    Location
    Los Angeles, California
    MS-Off Ver
    office 2010 pro
    Posts
    238

    Re: Counting Spaces Between Entries

    I understand that in reference to Question 1, but does that mean i will have to change the formula every year?
    Reply With QuoteRegister To Reply
  7. 03-23-2017, 09:11 AM #7
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,540

    Re: Counting Spaces Between Entries

    Try these formulas which reference year and entry values in D3:F3
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Formulas are applied in the attached file.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
  8. 03-27-2017, 12:32 AM #8
    RachelMads02
    RachelMads02 is offline
    Forum Contributor
    Join Date
    02-27-2015
    Location
    Los Angeles, California
    MS-Off Ver
    office 2010 pro
    Posts
    238

    Re: Counting Spaces Between Entries

    thank you!!!
    Reply With QuoteRegister To Reply
  9. 03-27-2017, 08:53 AM #9
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,540

    Re: Counting Spaces Between Entries

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.
    Reply With QuoteRegister To Reply
  10. 03-27-2017, 11:50 AM #10
    RachelMads02
    RachelMads02 is offline
    Forum Contributor
    Join Date
    02-27-2015
    Location
    Los Angeles, California
    MS-Off Ver
    office 2010 pro
    Posts
    238

    Re: Counting Spaces Between Entries

    The same to you too Jete!
    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. Only copying a single of multiple entries and counting qty of entries in a table
    By MML3329 in forum Excel General
    Replies: 1
    Last Post: 04-05-2016, 11:20 AM
  2. [SOLVED] Counting: words and blank spaces
    By RJL3313 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-02-2015, 09:17 AM
  3. [SOLVED] Drop down list. Entries including numbers and spaces
    By kollsvein in forum Excel General
    Replies: 15
    Last Post: 04-07-2015, 04:08 AM
  4. [SOLVED] Counting the number of entries in a column (but only once for consecutive entries)
    By 11416498 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-30-2014, 09:36 AM
  5. How to keep spaces between $ and other digits in cell entries?
    By Mirisage in forum Excel - New Users/Basics
    Replies: 9
    Last Post: 09-03-2013, 05:27 PM
  6. How can I ensure entries in a column are 16 chars long using spaces?
    By compact in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-18-2007, 06:11 AM
  7. [SOLVED] Counting the length of a string and adding spaces...
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-09-2006, 09:49 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