+ Reply to Thread
Results 1 to 7 of 7

Need Formula for MAX,MIN,START & END values for Yearly

  1. #1
    Registered User
    Join Date
    01-29-2020
    Location
    India
    MS-Off Ver
    2019
    Posts
    17

    Need Formula for MAX,MIN,START & END values for Yearly

    Hi All,

    I Need help to get formula for MAX,MIN,START & END values for Yearly. AS i have a sheet with daily values for years 2018 and 2019 as below
    01.JPG

    I need formula to get the results as below
    02.JPG



    can anyone please help on this..

    Thanks,
    Attached Files Attached Files
    Last edited by click2chaitu; 01-29-2020 at 04:45 AM.

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,787

    Re: Need Formula for MAX,MIN,START & END values for Yearly

    In Cell B2

    =MAX(IF(YEAR(Sheet1!$A$2:$A$1058)=$A2+0,Sheet1!$B$2:$B$1058,""))

    In Cell C2

    =MIN(IF(YEAR(Sheet1!$A$2:$A$1058)=$A2+0,Sheet1!$B$2:$B$1058,""))

    In Cell D2

    =VLOOKUP(MIN(IF(YEAR(Sheet1!$A$2:$A$1058)=$A2+0,Sheet1!$A$2:$A$1058,"")),Sheet1!$A$2:$B$1058,2,0)

    In Cell E2

    =VLOOKUP(MAX(IF(YEAR(Sheet1!$A$2:$A$1058)=$A2+0,Sheet1!$A$2:$A$1058,"")),Sheet1!$A$2:$B$1058,2,0)

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Attached Files Attached Files
    Cheers!
    Deep Dave

    www.NeedForExcel.com

  3. #3
    Registered User
    Join Date
    01-29-2020
    Location
    India
    MS-Off Ver
    2019
    Posts
    17

    Re: Need Formula for MAX,MIN,START & END values for Yearly

    Hi Dave,

    Thanks you so much for your work. It's working perfect except for cells B2 & E2 , reaming all cells are working perfect.

    Your Sheet with incorrect values
    01.JPG

    Correct values
    02.JPG

    Can you please check and confirm , it will be very help full to me.

  4. #4
    Registered User
    Join Date
    01-29-2020
    Location
    India
    MS-Off Ver
    2019
    Posts
    17

    Re: Need Formula for MAX,MIN,START & END values for Yearly

    and also can you please do the formulas in the same Sheet.

  5. #5
    Registered User
    Join Date
    01-29-2020
    Location
    India
    MS-Off Ver
    2019
    Posts
    17

    Re: Need Formula for MAX,MIN,START & END values for Yearly

    Sorry Dave, i haven't read your post fully, now i corrected the issue and also can you please do the formulas within the same Sheet (Sheet1).
    Last edited by click2chaitu; 01-29-2020 at 09:14 AM.

  6. #6
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,787

    Re: Need Formula for MAX,MIN,START & END values for Yearly

    Quote Originally Posted by click2chaitu View Post
    Sorry Dave, i haven't read your post fully, now i corrected the issue and also can you please do the formulas within the same Sheet (Sheet1).
    Please see the file attached
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    01-29-2020
    Location
    India
    MS-Off Ver
    2019
    Posts
    17

    Re: Need Formula for MAX,MIN,START & END values for Yearly

    Thanks Dave for your work it helped me a lot...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Yearly Quaterly Formula
    By farrukh in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-15-2019, 10:10 PM
  2. Formula to allocate revenues yearly
    By Myki in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-21-2017, 02:46 AM
  3. [SOLVED] Yearly Rent Escalation for multiple properties - variable start dates
    By md360 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-31-2017, 08:56 PM
  4. Formula to consolidate yearly data
    By rizmomin in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-13-2016, 03:02 PM
  5. Work out Yearly values
    By alex_boothby in forum Excel General
    Replies: 3
    Last Post: 09-30-2016, 06:10 AM
  6. Calculating yearly start dates ignoring leap years
    By Steve S. in forum Excel General
    Replies: 4
    Last Post: 02-25-2015, 04:39 PM
  7. [SOLVED] Formula for yearly cumulative amount
    By Peterino in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-28-2014, 08:13 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1