Custom functions

B

Brad Hull

I have an array that uses a (variable) number as an input and
calculates a "cost" as the output. On the same worksheet I have a
list of numbers (that will change periodically). I want to be able to
input each of these numbers into the array input cell and display the
resulting "cost" next to each number. I visualize 2 columns - the
first is the list of input values and the second is a "function" that
does two things: (1) sets the array input cell to the corresponding
input value, and (2) returns the "cost" calculation to "this" cell.
This all needs to happen automatically when the input value changes.
Help?
Thanks, Brad.
 
J

J.E. McGimpsey

I have an array that uses a (variable) number as an input and
calculates a "cost" as the output. On the same worksheet I have a
list of numbers (that will change periodically). I want to be able to
input each of these numbers into the array input cell and display the
resulting "cost" next to each number. I visualize 2 columns - the
first is the list of input values and the second is a "function" that
does two things: (1) sets the array input cell to the corresponding
input value, and (2) returns the "cost" calculation to "this" cell.
This all needs to happen automatically when the input value changes.
Help?
Thanks, Brad.

Since worksheet functions can only return values to their calling
cells, they can't set another cell's value. To do so requires a
macro.

However, it sounds to me like you could simply have as many
functions in your list of costs as you have in your list of inputs.
What is the relationship between input value and cost?
 
B

Brad Hull

The relationship thread is:
1. The input value (gallons of water) is split up into "rate groups".
2. The number of gallons in each rate group is multiplied by the
appropriate rate.
3. The sum of the costs of all the rate groups is added to a "base
(minimum) cost".
4. The "MIN" of this intermediate cost and the "maximum" charge for
the input gallons becomes the output value, or Final Cost".

An example, using an input value of 12 gallons:


A B C D E F
Gallons Gal/rate grp Rate Grp Gal/rate Rate UsageCost
(konst) (konst) (konst)

1 "input" 5 <5 5 1.00 5.00
2 7 5 <10 5 1.40 7.00
3 2 5 <15 2 2.00 4.00
4 -3 5 <20 0 2.20 0.00

1. A2=A1-B1
2. D2=IF(A2>0,MIN(A2,B2),0)
3. F2=D2*E2
4. Intermediate cost=SUM(F1:F17)+"base_rate"
7. Final cost=MIN(Intermediate cost,"maximum")

I agree that a macro is the best way to go; but, I have no experience
(yet!) with macros, and I would like the operation to be automatic
when I input to the array. Can that be done? (I'm using Excel X).

Thanks for your help,
Brad.
 
J

J.E. McGimpsey

The relationship thread is:
1. The input value (gallons of water) is split up into "rate groups".
2. The number of gallons in each rate group is multiplied by the
appropriate rate.
3. The sum of the costs of all the rate groups is added to a "base
(minimum) cost".
4. The "MIN" of this intermediate cost and the "maximum" charge for
the input gallons becomes the output value, or Final Cost".

An example, using an input value of 12 gallons:


A B C D E F
Gallons Gal/rate grp Rate Grp Gal/rate Rate UsageCost
(konst) (konst) (konst)

1 "input" 5 <5 5 1.00 5.00
2 7 5 <10 5 1.40 7.00
3 2 5 <15 2 2.00 4.00
4 -3 5 <20 0 2.20 0.00

1. A2=A1-B1
2. D2=IF(A2>0,MIN(A2,B2),0)
3. F2=D2*E2
4. Intermediate cost=SUM(F1:F17)+"base_rate"
7. Final cost=MIN(Intermediate cost,"maximum")

I agree that a macro is the best way to go; but, I have no experience
(yet!) with macros, and I would like the operation to be automatic
when I input to the array. Can that be done? (I'm using Excel X).

No need for a macro - or even interim calculations - just do it in
one cell.

Try:

Final cost = MIN(maximum, base_rate + SUMPRODUCT(--(A1>{0,5,10,15}),
(A1-{0,5,10,15}), {1,0.4,0.6,0.2}))

or, better, have your gallons and *incremental rate* in a table, so
that the rates can be easily changed:


J K L
1 Gals Rate Incr. Rate
2 0 1.00 =K2 <-- 1.00
3 5 1.40 =K3-K2 <-- 1.40 - 1.00 = 0.40
4 10 2.00 =K4-K3 <-- 2.00 - 1.40 = 0.60
5 15 2.20 =K5-K4 <-- 2.20 - 2.00 = 0.20

Then

Final cost = MIN(maximum, base_rate + SUMPRODUCT(--(A1>$J$1:$J$4),
(A1-$J$1:$J$4), $L$1:$L$4))

For a discussion of why this works, see

http://www.mcgimpsey.com/excel/taxvariablerate.html

If you have a list of usages, copy the Final Cost calculation down
the list (i.e., substuting list values for A1)
 
B

Brad Hull

J.E. McGimpsey said:
No need for a macro - or even interim calculations - just do it in
one cell.

Try:

Final cost = MIN(maximum, base_rate + SUMPRODUCT(--(A1>{0,5,10,15}),
(A1-{0,5,10,15}), {1,0.4,0.6,0.2}))

or, better, have your gallons and *incremental rate* in a table, so
that the rates can be easily changed:


J K L
1 Gals Rate Incr. Rate
2 0 1.00 =K2 <-- 1.00
3 5 1.40 =K3-K2 <-- 1.40 - 1.00 = 0.40
4 10 2.00 =K4-K3 <-- 2.00 - 1.40 = 0.60
5 15 2.20 =K5-K4 <-- 2.20 - 2.00 = 0.20

Then

Final cost = MIN(maximum, base_rate + SUMPRODUCT(--(A1>$J$1:$J$4),
(A1-$J$1:$J$4), $L$1:$L$4))

For a discussion of why this works, see

http://www.mcgimpsey.com/excel/taxvariablerate.html

If you have a list of usages, copy the Final Cost calculation down
the list (i.e., substuting list values for A1)

Thank you for your help - works like a charm. Amazing how close water
usage and income tax rates are . . . ! Once I got my references
straightened out (I also needed an embedded VLOOKUP), it is cooking!

Thanks again,
Brad.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top