+ Reply to Thread
Results 1 to 5 of 5

Quadrant calculation formulas based on single cell value

  1. #1
    Registered User
    Join Date
    03-27-2018
    Location
    NYC, USA
    MS-Off Ver
    Excel 2019 64-bit on Windows
    Posts
    21

    Post Quadrant calculation formulas based on single cell value

    Hi all,

    Apologies is this post/thread is redundant as I could not find exactly what I needed in previous threads.

    Please see attached.

    I have a field of data from different quarters including budget information, and to save time and energy I am hoping to find formulas and calculations in cells I6:K9 that are based on which quarter is entered in line 4 of the respective column.

    Some of the calculations are easier than others, but most are too complex for my current skill set.

    If anyone has any solutions or recommendations please let me know.

    Thank you in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,006

    Re: Quadrant calculation formulas based on single cell value

    Here is what you can do. IN D5 to G5 add the quarters end dates, then in J4 and K4: =I4
    In I6 and down: =SUMPRODUCT(--($D$5:$G$5<=HLOOKUP($I$4,$D$4:$G$5,2,0)),D6:G6)
    In J6: =SUMPRODUCT(--($D$4:$G$4=$J$4),D6:G6)/SUM(D6:G6)
    Attached Files Attached Files
    Click the * to say thanks.

  3. #3
    Registered User
    Join Date
    03-27-2018
    Location
    NYC, USA
    MS-Off Ver
    Excel 2019 64-bit on Windows
    Posts
    21

    Post Re: Quadrant calculation formulas based on single cell value

    Thank you Paul! This is perfect and working wonderfully within my larger sheet.

    I tried to recreate your sumproduct function for another task and it hasn't been working. I cannot figure out why. Could you please look at the simplified attachment and let me know where I am going wrong???

    Any advice would be greatly appreciated.

    Many thanks!
    Attached Files Attached Files

  4. #4
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,006

    Re: Quadrant calculation formulas based on single cell value

    =SUMPRODUCT((Sheet2!$A$4:$A$16=$A$2)*(Sheet2!$B$1:$U$1=$B5)*(Sheet2!$B$3:$U$3=C$4)*(Sheet2!$B$2:$U$2=$A$3)*Sheet2!$B$4:$U$16)
    Try the above. But be careful, cause you don't have actual numbers for 2017, but text. That will mess up the formula

  5. #5
    Registered User
    Join Date
    03-27-2018
    Location
    NYC, USA
    MS-Off Ver
    Excel 2019 64-bit on Windows
    Posts
    21

    Re: Quadrant calculation formulas based on single cell value

    It took some editing to make this work in my larger sheet but your warnings were apt and your formula spot on. Thank you!!!

+ 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] Omitting cells with formulas in single row from calculation.
    By Strogg in forum Excel General
    Replies: 6
    Last Post: 09-03-2018, 04:26 PM
  2. Replies: 6
    Last Post: 01-23-2018, 04:49 AM
  3. Calculation in a single cell
    By atharkhanpk in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-23-2018, 02:54 AM
  4. [SOLVED] Extract single cell data based on a single cell entry
    By farmerdoode in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-05-2013, 11:11 PM
  5. Single cell array calculation
    By NyoGoat in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-26-2013, 03:53 PM
  6. [SOLVED] Quadrant colour based on cell value
    By RoyLittle0 in forum Excel General
    Replies: 14
    Last Post: 05-04-2013, 12:40 PM
  7. Exclude a single cell from a calculation
    By kdecyk in forum Excel General
    Replies: 2
    Last Post: 05-23-2011, 04:40 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