Please help with Excel formula problem!

I've spent hours and hours trying to figure this out, with no luck. I'd
be PROFOUNDLY grateful if anybody can help.

You have several cities, say 10, requesting various amounts of funding.
You, the fund source, have a total of $1,500,000 in funds to give them.

However, the total amount they're all requesting is, say, $1,655,502.
You don't have enough; you only have $1,500,000.

1,500,000/1,655,502 = .9061.

So, you grant EACH city 90.61% of what it requested, and all the grants
total up to $1,500,000.

No problem with that one, you can throw up a little spreadsheet in a
few seconds. But here's the twist:

You want to do it PROGRESSIVELY, where the city with the smallest
request will always get the highest percentage of its request, and the
city with the largest request will get the lowest percentage. I'm
trying to come up with a way of distributing the imaginary funds so
that the large requestors won't suck up all the money. Sort of like tax
brackets...

It has to be computed precisely by the exact amount they're requesting.
Perhaps some kind of grouping is a possibility.

There's also the issue of deciding how far to range the grants. For
example, instead of everybody getting 90.61%, they could get from 95%
to 85%. Or 100% to 70%. Or..... ??

I've been trying to come up with a formula for this - it'd just be
another column on the spreadsheet - but can't for the LIFE of me figure
out a way to approach it. Can anybody help?

Many thanks
Ron M.

I would look at a VLOOKUP table with the Requested Amounts in one column
and the Percent to get in the next..........

Vaya con Dios,
Cuck, CABGx3


"Ron M." <[email protected]> wrote in message
news:[email protected]...
> I've spent hours and hours trying to figure this out, with no luck. I'd
> be PROFOUNDLY grateful if anybody can help.
>
> You have several cities, say 10, requesting various amounts of funding.
> You, the fund source, have a total of $1,500,000 in funds to give them.
>
> However, the total amount they're all requesting is, say, $1,655,502.
> You don't have enough; you only have $1,500,000.
>
> 1,500,000/1,655,502 = .9061.
>
> So, you grant EACH city 90.61% of what it requested, and all the grants
> total up to $1,500,000.
>
> No problem with that one, you can throw up a little spreadsheet in a
> few seconds. But here's the twist:
>
> You want to do it PROGRESSIVELY, where the city with the smallest
> request will always get the highest percentage of its request, and the
> city with the largest request will get the lowest percentage. I'm
> trying to come up with a way of distributing the imaginary funds so
> that the large requestors won't suck up all the money. Sort of like tax
> brackets...
>
> It has to be computed precisely by the exact amount they're requesting.
> Perhaps some kind of grouping is a possibility.
>
> There's also the issue of deciding how far to range the grants. For
> example, instead of everybody getting 90.61%, they could get from 95%
> to 85%. Or 100% to 70%. Or..... ??
>
> I've been trying to come up with a formula for this - it'd just be
> another column on the spreadsheet - but can't for the LIFE of me figure
> out a way to approach it. Can anybody help?
>
> Many thanks
> Ron M.
>

"CLR" <[email protected]> wrote in message
news:e0PY4%[email protected]...
> I would look at a VLOOKUP table with the Requested Amounts in one column
> and the Percent to get in the next..........
>
> Vaya con Dios,
> Cuck, CABGx3

You've suddenly lost your H :-)

Ack. Sorry, I have NO idea what you're talking about.

Ron M.

CLR wrote:
> I would look at a VLOOKUP table with the Requested Amounts in one
column
> and the Percent to get in the next..........
>
> Vaya con Dios,
> Cuck, CABGx3
>
>
> "Ron M." <[email protected]> wrote in message
> news:[email protected]...
> > I've spent hours and hours trying to figure this out, with no luck.
I'd
> > be PROFOUNDLY grateful if anybody can help.
> >
> > You have several cities, say 10, requesting various amounts of
funding.
> > You, the fund source, have a total of $1,500,000 in funds to give
them.
> >
> > However, the total amount they're all requesting is, say,
$1,655,502.
> > You don't have enough; you only have $1,500,000.
> >
> > 1,500,000/1,655,502 = .9061.
> >
> > So, you grant EACH city 90.61% of what it requested, and all the
grants
> > total up to $1,500,000.
> >
> > No problem with that one, you can throw up a little spreadsheet in
a
> > few seconds. But here's the twist:
> >
> > You want to do it PROGRESSIVELY, where the city with the smallest
> > request will always get the highest percentage of its request, and
the
> > city with the largest request will get the lowest percentage. I'm
> > trying to come up with a way of distributing the imaginary funds so
> > that the large requestors won't suck up all the money. Sort of like
tax
> > brackets...
> >
> > It has to be computed precisely by the exact amount they're
requesting.
> > Perhaps some kind of grouping is a possibility.
> >
> > There's also the issue of deciding how far to range the grants. For
> > example, instead of everybody getting 90.61%, they could get from
95%
> > to 85%. Or 100% to 70%. Or..... ??
> >
> > I've been trying to come up with a formula for this - it'd just be
> > another column on the spreadsheet - but can't for the LIFE of me
figure
> > out a way to approach it. Can anybody help?
> >
> > Many thanks
> > Ron M.
> >

LOL..........'deed I did <g>...........old fingers getting tired I
reckon...........

Vaya con Dios,
Chuck, CABGx3


"Bob Phillips" <[email protected]> wrote in message
news:#[email protected]...
>
> "CLR" <[email protected]> wrote in message
> news:e0PY4%[email protected]...
> > I would look at a VLOOKUP table with the Requested Amounts in one
column
> > and the Percent to get in the next..........
> >
> > Vaya con Dios,
> > Cuck, CABGx3
>
> You've suddenly lost your H :-)
>
>

Hi Ron.........
I don't understand, nor even agree with your concept about "giving those who
ask for less a larger percentage than those that ask for more", but I'm not
here to argue that point.........given that it is your concept, and
something you want to do, is good enough........but the percentages you
assign to whatever dollar values is a purely subjective thing.......you have
to decide what they will be and where the breaks will be. I was only
offering an Excel-way for you to automatically associate those percentages
to the requested dollar values by each city........that way being with a
VLOOKUP table and formula. For a couple of assumptions, lets say your 10
cities are in cells A1:A10, and their requested amounts are in
B1:B10..........then in K1 put 50,000 and fill that column down with 50000
increments to your 1500000 in K30..........then in L1:L30, insert whatever
Percentage you wish to assign to each value. It's ok for the same
percentage to apply to several increments if you wish. Then highlight
K1:L30 and do Insert > Name > Define, and type PercentRange in the small
window > OK.
Then in C1 put =VLOOKUP(A1,PercentRange,2,True) and copy it down to
B10...........now you have your selected percentage associated with each
request. If the requests should change, the percentages will adjust
automatically. In D1 put =B1*C1 and copy down to D10 Then sum D1:D10 and
see how it compares to your 1,500,000 available........adjust the
percentages in L1:L30 accordingly until your goal is met and your selections
are satisfactory.

hth
Vaya con Dios,
Chuck, CABGx3

..


"Ron M." <[email protected]> wrote in message
news:[email protected]...
> Ack. Sorry, I have NO idea what you're talking about.
>
> Ron M.
>
> CLR wrote:
> > I would look at a VLOOKUP table with the Requested Amounts in one
> column
> > and the Percent to get in the next..........
> >
> > Vaya con Dios,
> > Cuck, CABGx3
> >
> >
> > "Ron M." <[email protected]> wrote in message
> > news:[email protected]...
> > > I've spent hours and hours trying to figure this out, with no luck.
> I'd
> > > be PROFOUNDLY grateful if anybody can help.
> > >
> > > You have several cities, say 10, requesting various amounts of
> funding.
> > > You, the fund source, have a total of $1,500,000 in funds to give
> them.
> > >
> > > However, the total amount they're all requesting is, say,
> $1,655,502.
> > > You don't have enough; you only have $1,500,000.
> > >
> > > 1,500,000/1,655,502 = .9061.
> > >
> > > So, you grant EACH city 90.61% of what it requested, and all the
> grants
> > > total up to $1,500,000.
> > >
> > > No problem with that one, you can throw up a little spreadsheet in
> a
> > > few seconds. But here's the twist:
> > >
> > > You want to do it PROGRESSIVELY, where the city with the smallest
> > > request will always get the highest percentage of its request, and
> the
> > > city with the largest request will get the lowest percentage. I'm
> > > trying to come up with a way of distributing the imaginary funds so
> > > that the large requestors won't suck up all the money. Sort of like
> tax
> > > brackets...
> > >
> > > It has to be computed precisely by the exact amount they're
> requesting.
> > > Perhaps some kind of grouping is a possibility.
> > >
> > > There's also the issue of deciding how far to range the grants. For
> > > example, instead of everybody getting 90.61%, they could get from
> 95%
> > > to 85%. Or 100% to 70%. Or..... ??
> > >
> > > I've been trying to come up with a formula for this - it'd just be
> > > another column on the spreadsheet - but can't for the LIFE of me
> figure
> > > out a way to approach it. Can anybody help?
> > >
> > > Many thanks
> > > Ron M.
> > >
>

Thanks. Let me see if I can make that work. I'm a pretty experienced
Excel user, but I have zero experience with things like VLookup.

The table below illustrates it. You only have 1,000,000 to give, but
they're requesting much more: 1,769,430. You don't have enough money to
give everybody what they're requesting.

1,000,000/1,760,430 - .5652, so you give everybody 56.52% of their
request.

Requested % of Granted % of
total request
6,136.00 0.35 3,467.78 56.52 Dallas
11,234.00 0.63 6,348.94 56.52 Houston
18,356.00 1.04 10,373.96 56.52 Austin
112,562.00 6.36 63,614.84 56.52 San Antonio
113,374.00 6.41 64,073.74 56.52 Lubbock
115,134.00 6.51 65,068.41 56.52 El Paso
146,251.00 8.27 82,654.30 56.52 Fort Worth
293,561.00 16.59 165,907.10 56.52 Amarillo
369,471.00 20.88 208,807.92 56.52 Corpus Christi
583,351.00 32.97 329,683.01 56.52 Abilene

1,769,430.00 100.00 1,000,000.00

What I want to do is grant the smaller requestors a larger percentage
of their request, instead of granting everybody 56.52%. For example,
Dallas might get, say, 90% of their request. San Antonio might get 84%.
Abilene, the largest requestor, might get, say, 65%.

There are two interrelated issues here:

1. The range of the grant percentages. In the table above, it could be
narrow (e.g. Dallas 58% to Abilene 54%) or it could be wide (e.g.
Dallas 98% to Abilene 32%). I'm just making up those percentages, but
you get the picture. It would still total 1,000,000.

2. The range of the request amounts is subjectively tied to #1. If they
were all close together, (e.g. 364,586, 364,923, 364,188...)then it
wouldn't make sense to have a huge grant spread like 30%-to-99%. I
REALLY wish I could find some way to have this determination worked
into the formula, but it's simply beyond me... (-;

Don't worry, this isn't going to actually happen - I'm just using
cities as an example. The actual spreadsheet, if this system is
adopted, will have several hundred requests, by the way. I'm trying to
fix it so the only things entered will be the request amounts and the
total amount available. Everything else would be generated with
formulas.

Thanks a heap,
Ron


CLR wrote:
> Hi Ron.........
> I don't understand, nor even agree with your concept about "giving
those who
> ask for less a larger percentage than those that ask for more", but
I'm not
> here to argue that point.........given that it is your concept, and
> something you want to do, is good enough........but the percentages
you
> assign to whatever dollar values is a purely subjective
thing.......you have
> to decide what they will be and where the breaks will be. I was only
> offering an Excel-way for you to automatically associate those
percentages
> to the requested dollar values by each city........that way being
with a
> VLOOKUP table and formula. For a couple of assumptions, lets say
your 10
> cities are in cells A1:A10, and their requested amounts are in
> B1:B10..........then in K1 put 50,000 and fill that column down with
50000
> increments to your 1500000 in K30..........then in L1:L30, insert
whatever
> Percentage you wish to assign to each value. It's ok for the same
> percentage to apply to several increments if you wish. Then
highlight
> K1:L30 and do Insert > Name > Define, and type PercentRange in the
small
> window > OK.
> Then in C1 put =VLOOKUP(A1,PercentRange,2,True) and copy it down to
> B10...........now you have your selected percentage associated with
each
> request. If the requests should change, the percentages will adjust
> automatically. In D1 put =B1*C1 and copy down to D10 Then sum
D1:D10 and
> see how it compares to your 1,500,000 available........adjust the
> percentages in L1:L30 accordingly until your goal is met and your
selections
> are satisfactory.
>
> hth
> Vaya con Dios,
> Chuck, CABGx3
>
> .
>
>
> "Ron M." <[email protected]> wrote in message
> news:[email protected]...
> > Ack. Sorry, I have NO idea what you're talking about.
> >
> > Ron M.
> >
> > CLR wrote:
> > > I would look at a VLOOKUP table with the Requested Amounts in
one
> > column
> > > and the Percent to get in the next..........
> > >
> > > Vaya con Dios,
> > > Cuck, CABGx3
> > >
> > >
> > > "Ron M." <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I've spent hours and hours trying to figure this out, with no
luck.
> > I'd
> > > > be PROFOUNDLY grateful if anybody can help.
> > > >
> > > > You have several cities, say 10, requesting various amounts of
> > funding.
> > > > You, the fund source, have a total of $1,500,000 in funds to
give
> > them.
> > > >
> > > > However, the total amount they're all requesting is, say,
> > $1,655,502.
> > > > You don't have enough; you only have $1,500,000.
> > > >
> > > > 1,500,000/1,655,502 = .9061.
> > > >
> > > > So, you grant EACH city 90.61% of what it requested, and all
the
> > grants
> > > > total up to $1,500,000.
> > > >
> > > > No problem with that one, you can throw up a little spreadsheet
in
> > a
> > > > few seconds. But here's the twist:
> > > >
> > > > You want to do it PROGRESSIVELY, where the city with the
smallest
> > > > request will always get the highest percentage of its request,
and
> > the
> > > > city with the largest request will get the lowest percentage.
I'm
> > > > trying to come up with a way of distributing the imaginary
funds so
> > > > that the large requestors won't suck up all the money. Sort of
like
> > tax
> > > > brackets...
> > > >
> > > > It has to be computed precisely by the exact amount they're
> > requesting.
> > > > Perhaps some kind of grouping is a possibility.
> > > >
> > > > There's also the issue of deciding how far to range the grants.
For
> > > > example, instead of everybody getting 90.61%, they could get
from
> > 95%
> > > > to 85%. Or 100% to 70%. Or..... ??
> > > >
> > > > I've been trying to come up with a formula for this - it'd just
be
> > > > another column on the spreadsheet - but can't for the LIFE of
me
> > figure
> > > > out a way to approach it. Can anybody help?
> > > >
> > > > Many thanks
> > > > Ron M.
> > > >
> >

"Ron M." <[email protected]> wrote in message
news:[email protected]...
> Thanks. Let me see if I can make that work. I'm a pretty experienced
> Excel user, but I have zero experience with things like VLookup.
>
> The table below illustrates it. You only have 1,000,000 to give, but
> they're requesting much more: 1,769,430. You don't have enough money to
> give everybody what they're requesting.
>
> 1,000,000/1,760,430 - .5652, so you give everybody 56.52% of their
> request.
>
> Requested % of Granted % of
> total request
> 6,136.00 0.35 3,467.78 56.52 Dallas
> 11,234.00 0.63 6,348.94 56.52 Houston
> 18,356.00 1.04 10,373.96 56.52 Austin
> 112,562.00 6.36 63,614.84 56.52 San Antonio
> 113,374.00 6.41 64,073.74 56.52 Lubbock
> 115,134.00 6.51 65,068.41 56.52 El Paso
> 146,251.00 8.27 82,654.30 56.52 Fort Worth
> 293,561.00 16.59 165,907.10 56.52 Amarillo
> 369,471.00 20.88 208,807.92 56.52 Corpus Christi
> 583,351.00 32.97 329,683.01 56.52 Abilene
>
> 1,769,430.00 100.00 1,000,000.00
>
> What I want to do is grant the smaller requestors a larger percentage
> of their request, instead of granting everybody 56.52%. For example,
> Dallas might get, say, 90% of their request. San Antonio might get 84%.
> Abilene, the largest requestor, might get, say, 65%.
>
> There are two interrelated issues here:
>
> 1. The range of the grant percentages. In the table above, it could be
> narrow (e.g. Dallas 58% to Abilene 54%) or it could be wide (e.g.
> Dallas 98% to Abilene 32%). I'm just making up those percentages, but
> you get the picture. It would still total 1,000,000.
>
> 2. The range of the request amounts is subjectively tied to #1. If they
> were all close together, (e.g. 364,586, 364,923, 364,188...)then it
> wouldn't make sense to have a huge grant spread like 30%-to-99%. I
> REALLY wish I could find some way to have this determination worked
> into the formula, but it's simply beyond me... (-;
>
> Don't worry, this isn't going to actually happen - I'm just using
> cities as an example. The actual spreadsheet, if this system is
> adopted, will have several hundred requests, by the way. I'm trying to
> fix it so the only things entered will be the request amounts and the
> total amount available. Everything else would be generated with
> formulas.
>
> Thanks a heap,
> Ron
>
>
> CLR wrote:
>> Hi Ron.........
>> I don't understand, nor even agree with your concept about "giving
> those who
>> ask for less a larger percentage than those that ask for more", but
> I'm not
>> here to argue that point.........given that it is your concept, and
>> something you want to do, is good enough........but the percentages
> you
>> assign to whatever dollar values is a purely subjective
> thing.......you have
>> to decide what they will be and where the breaks will be. I was only
>> offering an Excel-way for you to automatically associate those
> percentages
>> to the requested dollar values by each city........that way being
> with a
>> VLOOKUP table and formula. For a couple of assumptions, lets say
> your 10
>> cities are in cells A1:A10, and their requested amounts are in
>> B1:B10..........then in K1 put 50,000 and fill that column down with
> 50000
>> increments to your 1500000 in K30..........then in L1:L30, insert
> whatever
>> Percentage you wish to assign to each value. It's ok for the same
>> percentage to apply to several increments if you wish. Then
> highlight
>> K1:L30 and do Insert > Name > Define, and type PercentRange in the
> small
>> window > OK.
>> Then in C1 put =VLOOKUP(A1,PercentRange,2,True) and copy it down to
>> B10...........now you have your selected percentage associated with
> each
>> request. If the requests should change, the percentages will adjust
>> automatically. In D1 put =B1*C1 and copy down to D10 Then sum
> D1:D10 and
>> see how it compares to your 1,500,000 available........adjust the
>> percentages in L1:L30 accordingly until your goal is met and your
> selections
>> are satisfactory.
>>
>> hth
>> Vaya con Dios,
>> Chuck, CABGx3
>>
>> .
>>
>>
>> "Ron M." <[email protected]> wrote in message
>> news:[email protected]...
>> > Ack. Sorry, I have NO idea what you're talking about.
>> >
>> > Ron M.
>> >
>> > CLR wrote:
>> > > I would look at a VLOOKUP table with the Requested Amounts in
> one
>> > column
>> > > and the Percent to get in the next..........
>> > >
>> > > Vaya con Dios,
>> > > Cuck, CABGx3
>> > >
>> > >
>> > > "Ron M." <[email protected]> wrote in message
>> > > news:[email protected]...
>> > > > I've spent hours and hours trying to figure this out, with no
> luck.
>> > I'd
>> > > > be PROFOUNDLY grateful if anybody can help.
>> > > >
>> > > > You have several cities, say 10, requesting various amounts of
>> > funding.
>> > > > You, the fund source, have a total of $1,500,000 in funds to
> give
>> > them.
>> > > >
>> > > > However, the total amount they're all requesting is, say,
>> > $1,655,502.
>> > > > You don't have enough; you only have $1,500,000.
>> > > >
>> > > > 1,500,000/1,655,502 = .9061.
>> > > >
>> > > > So, you grant EACH city 90.61% of what it requested, and all
> the
>> > grants
>> > > > total up to $1,500,000.
>> > > >
>> > > > No problem with that one, you can throw up a little spreadsheet
> in
>> > a
>> > > > few seconds. But here's the twist:
>> > > >
>> > > > You want to do it PROGRESSIVELY, where the city with the
> smallest
>> > > > request will always get the highest percentage of its request,
> and
>> > the
>> > > > city with the largest request will get the lowest percentage.
> I'm
>> > > > trying to come up with a way of distributing the imaginary
> funds so
>> > > > that the large requestors won't suck up all the money. Sort of
> like
>> > tax
>> > > > brackets...
>> > > >
>> > > > It has to be computed precisely by the exact amount they're
>> > requesting.
>> > > > Perhaps some kind of grouping is a possibility.
>> > > >
>> > > > There's also the issue of deciding how far to range the grants.
> For
>> > > > example, instead of everybody getting 90.61%, they could get
> from
>> > 95%
>> > > > to 85%. Or 100% to 70%. Or..... ??
>> > > >
>> > > > I've been trying to come up with a formula for this - it'd just
> be
>> > > > another column on the spreadsheet - but can't for the LIFE of
> me
>> > figure
>> > > > out a way to approach it. Can anybody help?
>> > > >
>> > > > Many thanks
>> > > > Ron M.
>> > > >
>> >
>

There are two extremes. You could give every city the same, or you could
pro-rata it on size, so its the same for every resident. You want some
formula in between. Well, there are an infinite number. So lets pick
something simple.

You could say that the grant is half for the city itself and half for the
residents. So divide all the money in two, and divide up one half equally
amongst each city and the other half equally by population. This would mean
the smallest city gets about twice as much per resident as in the biggest
cities.

One problem would be if two cities were really parts of one bigger city -
like Boston and Cambridge or Dallas and Fort Worth. They would get a bigger
funding that other single cities of the same size. But if you try and adjust
for geography, you will never get any formula right ...

Hi Peter...........
I wish to congratulate you on your submission. Of course the OP said he is
not using actual Cities, that was just for example, so the concern for
geography may not be a necessary, (but there may be others that are). But,
your concept of providing 50% of the distribution according to one factor,
and the other 50% according to another factor is very
interesting!..........WELL DONE!

Vaya con Dios,
Chuck, CABGx3


























"Peter Webb" <[email protected]> wrote in message
news:[email protected]...
>
> "Ron M." <[email protected]> wrote in message
> news:[email protected]...
> > Thanks. Let me see if I can make that work. I'm a pretty experienced
> > Excel user, but I have zero experience with things like VLookup.
> >
> > The table below illustrates it. You only have 1,000,000 to give, but
> > they're requesting much more: 1,769,430. You don't have enough money to
> > give everybody what they're requesting.
> >
> > 1,000,000/1,760,430 - .5652, so you give everybody 56.52% of their
> > request.
> >
> > Requested % of Granted % of
> > total request
> > 6,136.00 0.35 3,467.78 56.52 Dallas
> > 11,234.00 0.63 6,348.94 56.52 Houston
> > 18,356.00 1.04 10,373.96 56.52 Austin
> > 112,562.00 6.36 63,614.84 56.52 San Antonio
> > 113,374.00 6.41 64,073.74 56.52 Lubbock
> > 115,134.00 6.51 65,068.41 56.52 El Paso
> > 146,251.00 8.27 82,654.30 56.52 Fort Worth
> > 293,561.00 16.59 165,907.10 56.52 Amarillo
> > 369,471.00 20.88 208,807.92 56.52 Corpus Christi
> > 583,351.00 32.97 329,683.01 56.52 Abilene
> >
> > 1,769,430.00 100.00 1,000,000.00
> >
> > What I want to do is grant the smaller requestors a larger percentage
> > of their request, instead of granting everybody 56.52%. For example,
> > Dallas might get, say, 90% of their request. San Antonio might get 84%.
> > Abilene, the largest requestor, might get, say, 65%.
> >
> > There are two interrelated issues here:
> >
> > 1. The range of the grant percentages. In the table above, it could be
> > narrow (e.g. Dallas 58% to Abilene 54%) or it could be wide (e.g.
> > Dallas 98% to Abilene 32%). I'm just making up those percentages, but
> > you get the picture. It would still total 1,000,000.
> >
> > 2. The range of the request amounts is subjectively tied to #1. If they
> > were all close together, (e.g. 364,586, 364,923, 364,188...)then it
> > wouldn't make sense to have a huge grant spread like 30%-to-99%. I
> > REALLY wish I could find some way to have this determination worked
> > into the formula, but it's simply beyond me... (-;
> >
> > Don't worry, this isn't going to actually happen - I'm just using
> > cities as an example. The actual spreadsheet, if this system is
> > adopted, will have several hundred requests, by the way. I'm trying to
> > fix it so the only things entered will be the request amounts and the
> > total amount available. Everything else would be generated with
> > formulas.
> >
> > Thanks a heap,
> > Ron
> >
> >
> > CLR wrote:
> >> Hi Ron.........
> >> I don't understand, nor even agree with your concept about "giving
> > those who
> >> ask for less a larger percentage than those that ask for more", but
> > I'm not
> >> here to argue that point.........given that it is your concept, and
> >> something you want to do, is good enough........but the percentages
> > you
> >> assign to whatever dollar values is a purely subjective
> > thing.......you have
> >> to decide what they will be and where the breaks will be. I was only
> >> offering an Excel-way for you to automatically associate those
> > percentages
> >> to the requested dollar values by each city........that way being
> > with a
> >> VLOOKUP table and formula. For a couple of assumptions, lets say
> > your 10
> >> cities are in cells A1:A10, and their requested amounts are in
> >> B1:B10..........then in K1 put 50,000 and fill that column down with
> > 50000
> >> increments to your 1500000 in K30..........then in L1:L30, insert
> > whatever
> >> Percentage you wish to assign to each value. It's ok for the same
> >> percentage to apply to several increments if you wish. Then
> > highlight
> >> K1:L30 and do Insert > Name > Define, and type PercentRange in the
> > small
> >> window > OK.
> >> Then in C1 put =VLOOKUP(A1,PercentRange,2,True) and copy it down to
> >> B10...........now you have your selected percentage associated with
> > each
> >> request. If the requests should change, the percentages will adjust
> >> automatically. In D1 put =B1*C1 and copy down to D10 Then sum
> > D1:D10 and
> >> see how it compares to your 1,500,000 available........adjust the
> >> percentages in L1:L30 accordingly until your goal is met and your
> > selections
> >> are satisfactory.
> >>
> >> hth
> >> Vaya con Dios,
> >> Chuck, CABGx3
> >>
> >> .
> >>
> >>
> >> "Ron M." <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Ack. Sorry, I have NO idea what you're talking about.
> >> >
> >> > Ron M.
> >> >
> >> > CLR wrote:
> >> > > I would look at a VLOOKUP table with the Requested Amounts in
> > one
> >> > column
> >> > > and the Percent to get in the next..........
> >> > >
> >> > > Vaya con Dios,
> >> > > Cuck, CABGx3
> >> > >
> >> > >
> >> > > "Ron M." <[email protected]> wrote in message
> >> > > news:[email protected]...
> >> > > > I've spent hours and hours trying to figure this out, with no
> > luck.
> >> > I'd
> >> > > > be PROFOUNDLY grateful if anybody can help.
> >> > > >
> >> > > > You have several cities, say 10, requesting various amounts of
> >> > funding.
> >> > > > You, the fund source, have a total of $1,500,000 in funds to
> > give
> >> > them.
> >> > > >
> >> > > > However, the total amount they're all requesting is, say,
> >> > $1,655,502.
> >> > > > You don't have enough; you only have $1,500,000.
> >> > > >
> >> > > > 1,500,000/1,655,502 = .9061.
> >> > > >
> >> > > > So, you grant EACH city 90.61% of what it requested, and all
> > the
> >> > grants
> >> > > > total up to $1,500,000.
> >> > > >
> >> > > > No problem with that one, you can throw up a little spreadsheet
> > in
> >> > a
> >> > > > few seconds. But here's the twist:
> >> > > >
> >> > > > You want to do it PROGRESSIVELY, where the city with the
> > smallest
> >> > > > request will always get the highest percentage of its request,
> > and
> >> > the
> >> > > > city with the largest request will get the lowest percentage.
> > I'm
> >> > > > trying to come up with a way of distributing the imaginary
> > funds so
> >> > > > that the large requestors won't suck up all the money. Sort of
> > like
> >> > tax
> >> > > > brackets...
> >> > > >
> >> > > > It has to be computed precisely by the exact amount they're
> >> > requesting.
> >> > > > Perhaps some kind of grouping is a possibility.
> >> > > >
> >> > > > There's also the issue of deciding how far to range the grants.
> > For
> >> > > > example, instead of everybody getting 90.61%, they could get
> > from
> >> > 95%
> >> > > > to 85%. Or 100% to 70%. Or..... ??
> >> > > >
> >> > > > I've been trying to come up with a formula for this - it'd just
> > be
> >> > > > another column on the spreadsheet - but can't for the LIFE of
> > me
> >> > figure
> >> > > > out a way to approach it. Can anybody help?
> >> > > >
> >> > > > Many thanks
> >> > > > Ron M.
> >> > > >
> >> >
> >
>
> There are two extremes. You could give every city the same, or you could
> pro-rata it on size, so its the same for every resident. You want some
> formula in between. Well, there are an infinite number. So lets pick
> something simple.
>
> You could say that the grant is half for the city itself and half for the
> residents. So divide all the money in two, and divide up one half equally
> amongst each city and the other half equally by population. This would
mean
> the smallest city gets about twice as much per resident as in the biggest
> cities.
>
> One problem would be if two cities were really parts of one bigger city -
> like Boston and Cambridge or Dallas and Fort Worth. They would get a
bigger
> funding that other single cities of the same size. But if you try and
adjust
> for geography, you will never get any formula right ...
>
>
>

Hi Ron............

Well, I really-really like Peter's suggestion of making each grant a
composite of different factors. He suggested two, but it could be several.
This would give a rather complex formuls, (but easy for Excel) as to how the
Grants are calculated. I remember one time a looong time ago in a land far
away, I was using Lotus 123 to do some calculations on Horse Race
Handicapping. There were several contributing factors as to why one horse
should be picked to win over another. The interesting part was to give each
factor a Percentage weight (of 100%) and then blend them together to make
the final outcome in accordance with your wishes. I recommend that you
decide which factors in your situation you wish to be part of your
conclusion, and then give a weight to each of them, subjectively of course
to start, and then massage and blend them all together to come up with a
final grant amount.........and then factor it by yet another percentage if
it exceeds your available amount. You may wish to only use the
"RequestedAmount*.95%" as a MAX amount, and maybe "RequestedAmount*50%" as a
MIN amount, or something like that..........all sorts of
possibilities........I would like to hear what you finally come up with
tho........most interesting project..........

hth
Vaya con Dios,
Chuck, CABGx3






"Ron M." <[email protected]> wrote in message
news:[email protected]...
> Thanks. Let me see if I can make that work. I'm a pretty experienced
> Excel user, but I have zero experience with things like VLookup.
>
> The table below illustrates it. You only have 1,000,000 to give, but
> they're requesting much more: 1,769,430. You don't have enough money to
> give everybody what they're requesting.
>
> 1,000,000/1,760,430 - .5652, so you give everybody 56.52% of their
> request.
>
> Requested % of Granted % of
> total request
> 6,136.00 0.35 3,467.78 56.52 Dallas
> 11,234.00 0.63 6,348.94 56.52 Houston
> 18,356.00 1.04 10,373.96 56.52 Austin
> 112,562.00 6.36 63,614.84 56.52 San Antonio
> 113,374.00 6.41 64,073.74 56.52 Lubbock
> 115,134.00 6.51 65,068.41 56.52 El Paso
> 146,251.00 8.27 82,654.30 56.52 Fort Worth
> 293,561.00 16.59 165,907.10 56.52 Amarillo
> 369,471.00 20.88 208,807.92 56.52 Corpus Christi
> 583,351.00 32.97 329,683.01 56.52 Abilene
>
> 1,769,430.00 100.00 1,000,000.00
>
> What I want to do is grant the smaller requestors a larger percentage
> of their request, instead of granting everybody 56.52%. For example,
> Dallas might get, say, 90% of their request. San Antonio might get 84%.
> Abilene, the largest requestor, might get, say, 65%.
>
> There are two interrelated issues here:
>
> 1. The range of the grant percentages. In the table above, it could be
> narrow (e.g. Dallas 58% to Abilene 54%) or it could be wide (e.g.
> Dallas 98% to Abilene 32%). I'm just making up those percentages, but
> you get the picture. It would still total 1,000,000.
>
> 2. The range of the request amounts is subjectively tied to #1. If they
> were all close together, (e.g. 364,586, 364,923, 364,188...)then it
> wouldn't make sense to have a huge grant spread like 30%-to-99%. I
> REALLY wish I could find some way to have this determination worked
> into the formula, but it's simply beyond me... (-;
>
> Don't worry, this isn't going to actually happen - I'm just using
> cities as an example. The actual spreadsheet, if this system is
> adopted, will have several hundred requests, by the way. I'm trying to
> fix it so the only things entered will be the request amounts and the
> total amount available. Everything else would be generated with
> formulas.
>
> Thanks a heap,
> Ron
>
>
> CLR wrote:
> > Hi Ron.........
> > I don't understand, nor even agree with your concept about "giving
> those who
> > ask for less a larger percentage than those that ask for more", but
> I'm not
> > here to argue that point.........given that it is your concept, and
> > something you want to do, is good enough........but the percentages
> you
> > assign to whatever dollar values is a purely subjective
> thing.......you have
> > to decide what they will be and where the breaks will be. I was only
> > offering an Excel-way for you to automatically associate those
> percentages
> > to the requested dollar values by each city........that way being
> with a
> > VLOOKUP table and formula. For a couple of assumptions, lets say
> your 10
> > cities are in cells A1:A10, and their requested amounts are in
> > B1:B10..........then in K1 put 50,000 and fill that column down with
> 50000
> > increments to your 1500000 in K30..........then in L1:L30, insert
> whatever
> > Percentage you wish to assign to each value. It's ok for the same
> > percentage to apply to several increments if you wish. Then
> highlight
> > K1:L30 and do Insert > Name > Define, and type PercentRange in the
> small
> > window > OK.
> > Then in C1 put =VLOOKUP(A1,PercentRange,2,True) and copy it down to
> > B10...........now you have your selected percentage associated with
> each
> > request. If the requests should change, the percentages will adjust
> > automatically. In D1 put =B1*C1 and copy down to D10 Then sum
> D1:D10 and
> > see how it compares to your 1,500,000 available........adjust the
> > percentages in L1:L30 accordingly until your goal is met and your
> selections
> > are satisfactory.
> >
> > hth
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> > .
> >
> >
> > "Ron M." <[email protected]> wrote in message
> > news:[email protected]...
> > > Ack. Sorry, I have NO idea what you're talking about.
> > >
> > > Ron M.
> > >
> > > CLR wrote:
> > > > I would look at a VLOOKUP table with the Requested Amounts in
> one
> > > column
> > > > and the Percent to get in the next..........
> > > >
> > > > Vaya con Dios,
> > > > Cuck, CABGx3
> > > >
> > > >
> > > > "Ron M." <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > I've spent hours and hours trying to figure this out, with no
> luck.
> > > I'd
> > > > > be PROFOUNDLY grateful if anybody can help.
> > > > >
> > > > > You have several cities, say 10, requesting various amounts of
> > > funding.
> > > > > You, the fund source, have a total of $1,500,000 in funds to
> give
> > > them.
> > > > >
> > > > > However, the total amount they're all requesting is, say,
> > > $1,655,502.
> > > > > You don't have enough; you only have $1,500,000.
> > > > >
> > > > > 1,500,000/1,655,502 = .9061.
> > > > >
> > > > > So, you grant EACH city 90.61% of what it requested, and all
> the
> > > grants
> > > > > total up to $1,500,000.
> > > > >
> > > > > No problem with that one, you can throw up a little spreadsheet
> in
> > > a
> > > > > few seconds. But here's the twist:
> > > > >
> > > > > You want to do it PROGRESSIVELY, where the city with the
> smallest
> > > > > request will always get the highest percentage of its request,
> and
> > > the
> > > > > city with the largest request will get the lowest percentage.
> I'm
> > > > > trying to come up with a way of distributing the imaginary
> funds so
> > > > > that the large requestors won't suck up all the money. Sort of
> like
> > > tax
> > > > > brackets...
> > > > >
> > > > > It has to be computed precisely by the exact amount they're
> > > requesting.
> > > > > Perhaps some kind of grouping is a possibility.
> > > > >
> > > > > There's also the issue of deciding how far to range the grants.
> For
> > > > > example, instead of everybody getting 90.61%, they could get
> from
> > > 95%
> > > > > to 85%. Or 100% to 70%. Or..... ??
> > > > >
> > > > > I've been trying to come up with a formula for this - it'd just
> be
> > > > > another column on the spreadsheet - but can't for the LIFE of
> me
> > > figure
> > > > > out a way to approach it. Can anybody help?
> > > > >
> > > > > Many thanks
> > > > > Ron M.
> > > > >
> > >
>

CLR wrote:
> Hi Ron.........
> I don't understand, nor even agree with your concept about "giving
those who
> ask for less a larger percentage than those that ask for more", but
I'm not
> here to argue that point.........given that it is your concept, and
> something you want to do, is good enough........but the percentages
you
> assign to whatever dollar values is a purely subjective
thing.......you have
> to decide what they will be and where the breaks will be. I was only
> offering an Excel-way for you to automatically associate those
percentages
> to the requested dollar values by each city........that way being
with a
> VLOOKUP table and formula. For a couple of assumptions, lets say
your 10
> cities are in cells A1:A10, and their requested amounts are in
> B1:B10..........then in K1 put 50,000 and fill that column down with
50000
> increments to your 1500000 in K30..........then in L1:L30, insert
whatever
> Percentage you wish to assign to each value. It's ok for the same
> percentage to apply to several increments if you wish. Then
highlight
> K1:L30 and do Insert > Name > Define, and type PercentRange in the
small
> window > OK.
> Then in C1 put =VLOOKUP(A1,PercentRange,2,True) and copy it down to
> B10...........now you have your selected percentage associated with
each
> request. If the requests should change, the percentages will adjust
> automatically. In D1 put =B1*C1 and copy down to D10 Then sum
D1:D10 and
> see how it compares to your 1,500,000 available........adjust the
> percentages in L1:L30 accordingly until your goal is met and your
selections
> are satisfactory.

Chuck, I did EXACTLY what you described above. In L1;l30, I put 100 in
L1, incrementing down to 70 in L30.

When I entered the VLOOKUP string you described above in C1, though, it
says "#N/A."

Ron M.

Sorry Ron........looks like I made a boo-boo.........should have been

=VLOOKUP(B1,PercentRange,2,True) instead of

=VLOOKUP(A1,PercentRange,2,True)

This time it should be ok, but let me know if not..........

Vaya con Dios,
Chuck, CABGx3



"Ron M." <[email protected]> wrote in message
news:[email protected]...
>
> CLR wrote:
> > Hi Ron.........
> > I don't understand, nor even agree with your concept about "giving
> those who
> > ask for less a larger percentage than those that ask for more", but
> I'm not
> > here to argue that point.........given that it is your concept, and
> > something you want to do, is good enough........but the percentages
> you
> > assign to whatever dollar values is a purely subjective
> thing.......you have
> > to decide what they will be and where the breaks will be. I was only
> > offering an Excel-way for you to automatically associate those
> percentages
> > to the requested dollar values by each city........that way being
> with a
> > VLOOKUP table and formula. For a couple of assumptions, lets say
> your 10
> > cities are in cells A1:A10, and their requested amounts are in
> > B1:B10..........then in K1 put 50,000 and fill that column down with
> 50000
> > increments to your 1500000 in K30..........then in L1:L30, insert
> whatever
> > Percentage you wish to assign to each value. It's ok for the same
> > percentage to apply to several increments if you wish. Then
> highlight
> > K1:L30 and do Insert > Name > Define, and type PercentRange in the
> small
> > window > OK.
> > Then in C1 put =VLOOKUP(A1,PercentRange,2,True) and copy it down to
> > B10...........now you have your selected percentage associated with
> each
> > request. If the requests should change, the percentages will adjust
> > automatically. In D1 put =B1*C1 and copy down to D10 Then sum
> D1:D10 and
> > see how it compares to your 1,500,000 available........adjust the
> > percentages in L1:L30 accordingly until your goal is met and your
> selections
> > are satisfactory.
>
> Chuck, I did EXACTLY what you described above. In L1;l30, I put 100 in
> L1, incrementing down to 70 in L30.
>
> When I entered the VLOOKUP string you described above in C1, though, it
> says "#N/A."
>
> Ron M.
>

No, still didn't work. Still get #N/A. Here's what I have in the
respective columns:

A1-A10: names of ten cities
B1-B10: their requests, sorted ascending
K1-K30: 50000 to 1500000, in 50,000 increments.
L1-L30: 1.0 to .71, in .01 increments

I tried it with L1-L30 done several different ways, like from 100 to
71, as well as 1.0 to .71.

I'm on the edge of my seat... (-;

Ron M.

Well, then it sounds like either your column B or column K is formatted as
TEXT even tho they may look like numbers..........

Vaya con Dios,
Chuck CABGx3


"Ron M." <[email protected]> wrote in message
news:[email protected]...
> No, still didn't work. Still get #N/A. Here's what I have in the
> respective columns:
>
> A1-A10: names of ten cities
> B1-B10: their requests, sorted ascending
> K1-K30: 50000 to 1500000, in 50,000 increments.
> L1-L30: 1.0 to .71, in .01 increments
>
> I tried it with L1-L30 done several different ways, like from 100 to
> 71, as well as 1.0 to .71.
>
> I'm on the edge of my seat... (-;
>
> Ron M.
>

Nope, it's numbers. I'm trying to find your email address so I can send
it to you as an attachment and you can look at it, but the username
part just says "crobe..."

Feel free to send me a note, then I can reply.

Ron

I've added a sample workbook to my web site that shows one approach you
could use. It lets you enter the amount available, the maximum
percentage to allocate, and the percentage spread.

http://www.contextures.com/excelfiles.html

Under Functions, look for 'Grant Distribution'

Ron M. wrote:
> CLR wrote:
>
>>Hi Ron.........
>>I don't understand, nor even agree with your concept about "giving
>
> those who
>
>>ask for less a larger percentage than those that ask for more", but
>
> I'm not
>
>>here to argue that point.........given that it is your concept, and
>>something you want to do, is good enough........but the percentages
>
> you
>
>>assign to whatever dollar values is a purely subjective
>
> thing.......you have
>
>>to decide what they will be and where the breaks will be. I was only
>>offering an Excel-way for you to automatically associate those
>
> percentages
>
>>to the requested dollar values by each city........that way being
>
> with a
>
>>VLOOKUP table and formula. For a couple of assumptions, lets say
>
> your 10
>
>>cities are in cells A1:A10, and their requested amounts are in
>>B1:B10..........then in K1 put 50,000 and fill that column down with
>
> 50000
>
>>increments to your 1500000 in K30..........then in L1:L30, insert
>
> whatever
>
>>Percentage you wish to assign to each value. It's ok for the same
>>percentage to apply to several increments if you wish. Then
>
> highlight
>
>>K1:L30 and do Insert > Name > Define, and type PercentRange in the
>
> small
>
>>window > OK.
>>Then in C1 put =VLOOKUP(A1,PercentRange,2,True) and copy it down to
>>B10...........now you have your selected percentage associated with
>
> each
>
>>request. If the requests should change, the percentages will adjust
>>automatically. In D1 put =B1*C1 and copy down to D10 Then sum
>
> D1:D10 and
>
>>see how it compares to your 1,500,000 available........adjust the
>>percentages in L1:L30 accordingly until your goal is met and your
>
> selections
>
>>are satisfactory.
>
>
> Chuck, I did EXACTLY what you described above. In L1;l30, I put 100 in
> L1, incrementing down to 70 in L30.
>
> When I entered the VLOOKUP string you described above in C1, though, it
> says "#N/A."
>
> Ron M.
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra, that looks great - but it doesn't seem to work right. The first
9 grants are EXACTLY .50% apart, and then Abilene suddenly plunges down
about 16%.

Ron M.

P.S. That's a nice web page; I bookmarked it instantly. Thanks for all
the work you obviously put into it.

Debra Dalgleish wrote:
> I've added a sample workbook to my web site that shows one approach
you
> could use. It lets you enter the amount available, the maximum
> percentage to allocate, and the percentage spread.
>
> http://www.contextures.com/excelfiles.html
>
> Under Functions, look for 'Grant Distribution'
>
> Ron M. wrote:
> > CLR wrote:
> >
> >>Hi Ron.........
> >>I don't understand, nor even agree with your concept about "giving
> >
> > those who
> >
> >>ask for less a larger percentage than those that ask for more", but
> >
> > I'm not
> >
> >>here to argue that point.........given that it is your concept, and
> >>something you want to do, is good enough........but the percentages
> >
> > you
> >
> >>assign to whatever dollar values is a purely subjective
> >
> > thing.......you have
> >
> >>to decide what they will be and where the breaks will be. I was
only
> >>offering an Excel-way for you to automatically associate those
> >
> > percentages
> >
> >>to the requested dollar values by each city........that way being
> >
> > with a
> >
> >>VLOOKUP table and formula. For a couple of assumptions, lets say
> >
> > your 10
> >
> >>cities are in cells A1:A10, and their requested amounts are in
> >>B1:B10..........then in K1 put 50,000 and fill that column down
with
> >
> > 50000
> >
> >>increments to your 1500000 in K30..........then in L1:L30, insert
> >
> > whatever
> >
> >>Percentage you wish to assign to each value. It's ok for the same
> >>percentage to apply to several increments if you wish. Then
> >
> > highlight
> >
> >>K1:L30 and do Insert > Name > Define, and type PercentRange in the
> >
> > small
> >
> >>window > OK.
> >>Then in C1 put =VLOOKUP(A1,PercentRange,2,True) and copy it down to
> >>B10...........now you have your selected percentage associated with
> >
> > each
> >
> >>request. If the requests should change, the percentages will
adjust
> >>automatically. In D1 put =B1*C1 and copy down to D10 Then sum
> >
> > D1:D10 and
> >
> >>see how it compares to your 1,500,000 available........adjust the
> >>percentages in L1:L30 accordingly until your goal is met and your
> >
> > selections
> >
> >>are satisfactory.
> >
> >
> > Chuck, I did EXACTLY what you described above. In L1;l30, I put 100
in
> > L1, incrementing down to 70 in L30.
> >
> > When I entered the VLOOKUP string you described above in C1,
though, it
> > says "#N/A."
> >
> > Ron M.
> >
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html

Ron, the last value is the difference between the total available, and
the first nine amounts allocated.

If you'd prefer that the last value be an equal percentage from the
previous city, you could drag down the formula from the cell above.

If all ten amounts are determined by formulas, you'll probably have to
do some manual tweaking to get the total to exactly equal the amount
available.

Ron M. wrote:
> Debra, that looks great - but it doesn't seem to work right. The first
> 9 grants are EXACTLY .50% apart, and then Abilene suddenly plunges down
> about 16%.
>
> Ron M.
>
> P.S. That's a nice web page; I bookmarked it instantly. Thanks for all
> the work you obviously put into it.
>
> Debra Dalgleish wrote:
>
>>I've added a sample workbook to my web site that shows one approach
>
> you
>
>>could use. It lets you enter the amount available, the maximum
>>percentage to allocate, and the percentage spread.
>>
>> http://www.contextures.com/excelfiles.html
>>
>>Under Functions, look for 'Grant Distribution'
>>
>>Ron M. wrote:
>>
>>>CLR wrote:
>>>
>>>
>>>>Hi Ron.........
>>>>I don't understand, nor even agree with your concept about "giving
>>>
>>>those who
>>>
>>>
>>>>ask for less a larger percentage than those that ask for more", but
>>>
>>>I'm not
>>>
>>>
>>>>here to argue that point.........given that it is your concept, and
>>>>something you want to do, is good enough........but the percentages
>>>
>>>you
>>>
>>>
>>>>assign to whatever dollar values is a purely subjective
>>>
>>>thing.......you have
>>>
>>>
>>>>to decide what they will be and where the breaks will be. I was
>>>
> only
>
>>>>offering an Excel-way for you to automatically associate those
>>>
>>>percentages
>>>
>>>
>>>>to the requested dollar values by each city........that way being
>>>
>>>with a
>>>
>>>
>>>>VLOOKUP table and formula. For a couple of assumptions, lets say
>>>
>>>your 10
>>>
>>>
>>>>cities are in cells A1:A10, and their requested amounts are in
>>>>B1:B10..........then in K1 put 50,000 and fill that column down
>>>
> with
>
>>>50000
>>>
>>>
>>>>increments to your 1500000 in K30..........then in L1:L30, insert
>>>
>>>whatever
>>>
>>>
>>>>Percentage you wish to assign to each value. It's ok for the same
>>>>percentage to apply to several increments if you wish. Then
>>>
>>>highlight
>>>
>>>
>>>>K1:L30 and do Insert > Name > Define, and type PercentRange in the
>>>
>>>small
>>>
>>>
>>>>window > OK.
>>>>Then in C1 put =VLOOKUP(A1,PercentRange,2,True) and copy it down to
>>>>B10...........now you have your selected percentage associated with
>>>
>>>each
>>>
>>>
>>>>request. If the requests should change, the percentages will
>>>
> adjust
>
>>>>automatically. In D1 put =B1*C1 and copy down to D10 Then sum
>>>
>>>D1:D10 and
>>>
>>>
>>>>see how it compares to your 1,500,000 available........adjust the
>>>>percentages in L1:L30 accordingly until your goal is met and your
>>>
>>>selections
>>>
>>>
>>>>are satisfactory.
>>>
>>>
>>>Chuck, I did EXACTLY what you described above. In L1;l30, I put 100
>>
> in
>
>>>L1, incrementing down to 70 in L30.
>>>
>>>When I entered the VLOOKUP string you described above in C1,
>>
> though, it
>
>>>says "#N/A."
>>>
>>>Ron M.
>>>
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html