Counting Two Number Sequences

Necroscope

Board Regular
Joined
Jul 7, 2004
Messages
72
Hello all. I hope you can help with this. It sounds simple in my head but I can't figure out the formula!

A1 and B1 have the numbers 100 and 199 in them. (It's a range of 100 ticket numbers.)
A2 and B2 have the numbers 500 and 599. (Another range of 100.)

In C1, I have typed in 120. What I want Excel to do is say that in order to meet the figure of 120, all tickets from the first batch need to be used and 20 tickets from the second batch need to be used. Ie ticket numbers 100 to 199 and 500 to 519 are used up.

The second part of the problem is that I'd also like to enter another number - for example 50 - and Excel would know to look at the available tickets from the second batch because the first batch is gone. I.e ticket numbers 520 to 569.

Is there a non-VBA way to do this?

TIA
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,431
Office Version
  1. 365
Platform
  1. Windows
Necroscope

I am sure there is. If I have understood correctly, then here is one way. I have ussed quite a few 'helper' columns so that the formulas don't become too long. Provided the formulas have been entered far enough down in these extra columns, the columns could be hidden if desired.

C3: =F3-D3
D3: =MIN(MAX(0,G3-$I$3),F3)
F3: =IF(COUNT(A3:B3)=2,B3-A3+1,"")
G3: =IF(F3="","",G2+F3)
I3: =SUM(H:H)

All formulas (except I3) copied down. The numbers you referred to as being entered in column C, I have entered in column H. However, all columns can be re-arranged as you see fit.

Was this anything like you wanted?
Mr Excel.xls
ABCDEFGHI
1Used from this batchRemaining from this batchTickets in BatchTotal TicketsUsedTotal Used
2
3100199100010010080140
4500599406010020010
5800899010010030030
611001199010010040020
7  
8  
Tickets
 

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
This might be what you want. Keeps a running total of the balance at each entry, so you can add batches of tickets/requirements below as required.

A2 has your start batch number
B2 has your end batch number
C2 has formula =B2-A2+1
D2 has your number eg. 120 required
E2 has formula =SUM($C$2:C100)-SUM($D$1:D2)
(watch the $ positions - can copy/paste the formula from here)
can copy formulas down.
 

Necroscope

Board Regular
Joined
Jul 7, 2004
Messages
72
Peter and Brian. Thanks to you both for your help. Both work great so I'll tinker around with what I ultimately want to produce and use your formula.

I appreciate your help.

Richard
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,548
Messages
5,838,038
Members
430,527
Latest member
MyFace2

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
Top