no artwork works with my songs

Q: Finding min/max number within a selected date range - Please HELP!

Hi all,

 

I am not very advanced at complex formulas however using this forum over the past few weeks thanks to everyone here, I have been able to create amazing automated data collection workbook. I have been searching for the final piece missing and from the two explanations I have found here so far, they are too complex for me to understand yet what I am wanting to achieve I thought was quite straight forward.

 

If anyone can help me with the formula to use in these empty boxes it will make my week!

 

FYI I am using a mac air that is all up to date.

Thanks.

 

Screen Shot 2016-10-03 at 12.53.29 PM.png

Posted on Oct 2, 2016 8:58 PM

Close

Q: Finding min/max number within a selected date range - Please HELP!

  • All replies
  • Helpful answers

  • by Dracik,

    Dracik Dracik Oct 2, 2016 9:40 PM in response to no artwork works with my songs
    Level 1 (4 points)
    Oct 2, 2016 9:40 PM in response to no artwork works with my songs

    Until there was an update to mac OS Sierra and new Numbers thif formula had been working for me. It's not anymore, therefore I am looking for the same answer on new Numbers. Take a look at the formula below and maybe it will give you a hint

    SUMIF(Table 1::$1:$1,">"&EOMONTH(DATE($A2,M$1,1),0),Table 1::$VP)

  • by no artwork works with my songs,

    no artwork works with my songs no artwork works with my songs Oct 2, 2016 11:43 PM in response to Dracik
    Level 1 (8 points)
    iTunes
    Oct 2, 2016 11:43 PM in response to Dracik

    Hi Dracik,

     

    Thanks for your reply. I have used date range before however not EOMONTH...

    Am I to manually type the date in where it says date?

    Do you have a screenshot of how it ought to look in the formula bar? Ie.Screen Shot 2016-10-03 at 3.38.53 PM.png

  • by Barry,

    Barry Barry Oct 3, 2016 12:14 AM in response to Dracik
    Level 7 (32,502 points)
    iWork
    Oct 3, 2016 12:14 AM in response to Dracik

    Hi Dracik,

     

    "this formula" will not produce the results requested by the OP, even if it does work. You are not looking for an answer to the same question, and should post your question as a New Topic.

     

    The OP wants to retrieve the MAXimum value and the MINimum value within a specific month, not the SUM of all values occurring after a specific date.

     

    Regards,

    Barry

  • by no artwork works with my songs,

    no artwork works with my songs no artwork works with my songs Oct 3, 2016 12:17 AM in response to Barry
    Level 1 (8 points)
    iTunes
    Oct 3, 2016 12:17 AM in response to Barry

    HI Barry,

     

    thank you for clarifying that. Can you help me?

     

    Thank you,

     

    Alicia.

  • by Barry,Solvedanswer

    Barry Barry Oct 3, 2016 1:26 AM in response to no artwork works with my songs
    Level 7 (32,502 points)
    iWork
    Oct 3, 2016 1:26 AM in response to no artwork works with my songs

    Hi no...

     

    Strange user name. If true, I hope this name is soon outdated.

     

    While (s)he hasn't answered your question, Dracik has provided a clue to the type of function needed. Unfortunately, although Numbers supports SUMIF, COUNTIF and AVERAGEIF (and the plural versions of the first two), it does not support MAXIF, MINIF, or the functions that would be really useful here, MAXIFS and MINIFS.

     

    There are ways to accomplishs this, though. Here's one:

    Screen Shot 2016-10-03 at 12.54.27 AM.png

    Table 1:

    Column A: A list of dates in chronological order.

    Column B: The 'month number' for each date, extracted by the formula below. This column is necessary to the working of the process, but does not need to be 'column B,' and may be hidden. As this table is for data entry, this calculation should probably be placed in column A, and hidden from the user to avoid accidentally damaging the formula.

    Column C: The number associated with each date in column A, and from which the minimum and maximum values for each month are to be retrieved. The cells are coloured manually as an aid to locating the group of values for each month.

     

    Formula:

    B2, and filled down to the last row of the table: =MONTH(A2)

     

    Table 2

     

    Column A: entered data. The names of the months, in calendar order.

    Column B: entered data. The number of the month corresponding to the name in column A.

    Columns C and S: Formulas to calculate the Minimum and Maximum value for each month.

     

    The two formulas are identical except for the use of MIN() in column C and MAX() in column D.

    Base formulas (These will return an error message for months in which not data exists)

     

    C2: =MIN(OFFSET(Table 1::$A$1,MATCH($B2,Table 1::$B,0)−1,2,COUNTIF(Table 1::$B,$B2)))

    D2: =MAX(OFFSET(Table 1::$A$1,MATCH($B2,Table 1::$B,0)−1,2,COUNTIF(Table 1::$B,$B2)))

     

    To prevent error messages in months where no data exists, IF has been used as a 'switch' to prevent calculation until at least one line of data exists for the month on 'this row'.

     

    C2: =IF(COUNTIF(Table 1::$B,$B2)<1,"",MIN(OFFSET(Table 1::$A$1,MATCH($B2,Table 1::$B,0)−1,2,COUNTIF(Table 1::$B,$B2))))

    D2: =IF(COUNTIF(Table 1::$B,$B2)<1,"",MAX(OFFSET(Table 1::$A$1,MATCH($B2,Table 1::$B,0)−1,2,COUNTIF(Table 1::$B,$B2))))

     

    Both formulas are filled down to the December row of their respective columns.

     

    Notes:

    Dates in Table 1 MUST be grouped by month.

    The table will handle one year's worth of data.

     

    Regards,

    Barry

  • by no artwork works with my songs,

    no artwork works with my songs no artwork works with my songs Oct 4, 2016 12:23 AM in response to Barry
    Level 1 (8 points)
    iTunes
    Oct 4, 2016 12:23 AM in response to Barry

    HI Barry,

     

    Alicia here again. Please disregard the username. I wondered why the username - "no album artworks with my songs" appeared on my file. I am not sure how it got there or how to remove it... sure as heck wouldn't have chose that on purpose and just tried to change it yet couldn't. I wasn't able to even select it to edit so apologies about that. Anyway back to the important stuff...

     

    Thanks for your detailed reply. You were extremely helpful and I gave it a thorough go and was able to get the MIN and MAX for the months. THANK YOU! I didn't use the 2nd option (IF) as I don't collect data with missing information at this stage.

     

    Another question arises to whether there is the same way of recording the fortnight number automatically using DATE...? If not, I can always record these numbers manually right for the purpose I need? My aim you see is to get fortnightly data.

     

    I am interested to know where you learnt to use/write such formulas? I would love to learn more myself yet find any youtube tutorials are all in excel (>_<).

     

    Thank you again, I am extremely grateful.

  • by Barry,

    Barry Barry Oct 4, 2016 1:17 AM in response to no artwork works with my songs
    Level 7 (32,502 points)
    iWork
    Oct 4, 2016 1:17 AM in response to no artwork works with my songs

    Hi Alicia,

     

    Regarding the user name: I suspect it happened when you came to these pages to ask your first question, and accidentally typed the question into the registration form.

     

    There's no function to calculate the fortnight number, but there is one ( WEEKNUM ) that will return the Week Number for a supplied date. It allows specifying Sunday of Monday as the start of the week. If fortnights start on either of those days, the fortnight number could be obtained using:

     

    =INT((WEEKNUM(A1,1)+1)/2)

     

    where A1 is a cell containing the date for which the fortnight number is being calculated. INT simply strips the fractional part of the quotient, leaving an integer value. The formula will return 1 for weeks 1 and 2, 2 for weeks 3 and 4, etc.

    It could be used to calculate the FNo. for an entered date, or for TODAY() (another function). To fix the number, you'd select the cell containing a copy of the formula, Copy, then go Edit > Paste formula results.

     

    Regarding learning—it's a lifelong thing. Most of the functions and formulas were learned by reading the manuals (or more recently, the notes in the function browser included with Numbers) and by trying things out. I enjoy puzzles, and treat many of the questions that come up here as puzzles to be solved through experimentation, reading, and paying attention to what others do to solve them.

     

    Regarding tutorials: Many of the functions in Excel are the same as those in Numbers. The large exceptions are the array functions that are supported in Excel, and Excel's ability to use formulas that can pull data from another document. So if you find an Excel tutorial that does something you need to do, watch it, then try it out in Numbers. If it works, great! If it doesn't, bring the question here—there may be a different means to the same end through Numbers.

     

    Regards,

    Barry