# Quadrant calculation formulas based on single cell value

1. ## 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.

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.  Register To Reply

2. ## 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)  Register To Reply

3. ## 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!  Register To Reply

4. ## 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  Register To Reply

5. ## 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!!!  Register To Reply

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