Please help with Excel formula problem!

[Ron M.]

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]

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

 

[Bob Phillips]

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

 

[Ron M.]

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

Ron M.

 

[CLR]

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

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

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 D110 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.]

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

 

[Peter Webb]

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

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

 

[CLR]

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

 

[CLR]

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.]

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 D110 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.

 

[CLR]

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.]

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.

 

[CLR]

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.]

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

 

[Debra Dalgleish]

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.]

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]

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.

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.

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'
only
with
adjust

in

though, it