Help With Calculations

ststern45

Well-known Member
Joined
Sep 17, 2005
Messages
976
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
In cell range R30 through R59 containing the values 1 through 30. (see attached image)
Cell range T30 through T59 contain various values and are sorted from low to high ( 1 through 16)
Disregard cell range V30 through V59.

Looking for calculations in cell range T30 through T59 that will do the following if possible:
The lowest value is 1 and the highest value is 16 (cell T59)
If I add the first 5 values from T30 through T34 they all contain a 1 so the total is 5
If I add the last 5 values from T55 through T59 the values are 9, 11, 12 ,12, 16 which total 60
So the the low value is 5 and the high value is 60.

If I enter the values starting with 5 and ending with 60 (5, 6, 7, 8, 9 ..... 60) in a new column I would like to know how many sets can be created.
Calculations would be in 5 increments
For example, starting in cells T30 though T34 (5 increments) they all contain the values 1 which totals 5.
In cell range T30 through T37 all contain the value 1.
The total would be 56 using the formula =combin(8,5)
So the sum 5 would total 56
The rest I'm still trying to figure out and looking for help.
Thanks in advance!!
 

Attachments

  • Image 1.jpg
    Image 1.jpg
    58.9 KB · Views: 11

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Part 2
The 2 images below is what I'm trying to figure out using another type of formula(s) which would be faster than what I used.
Cell range A2 through A57 contain the Sums 5 through 69
Cell range B2 through B57 contain the total set values for each of the sum values 5 through 60
These calculations I was able to figure out since the total number of values is 30 (1 through 30)
Creating sets for 30 numbers is 142,506
Doing the same calculations for larger sets such as 43 requires 962,590 sets which would take too long to calculate.

Thank you for the help in advance!!
 

Attachments

  • MrExcel 1.jpg
    MrExcel 1.jpg
    48.2 KB · Views: 7
  • MrExcel 2.jpg
    MrExcel 2.jpg
    49.7 KB · Views: 6
Upvote 0

Forum statistics

Threads
1,225,275
Messages
6,184,005
Members
453,204
Latest member
mamzy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top