Shonzi:
It seems to work with daily, but any other choice remains the same answer as
daily.
I included three more areas under A2 for family income, when I copy the cell
nothing seems to work but daily. I also increased the numbers to reflect a
yearly amount divided by 12, once again it only works with the daily choice.
Any ideas?
Thanks for your help.
George
- Show quoted text -
It turns out my original formula had an extra, unnecessary bit to it.
You don't need the INDEX function, but it will work with it in there.
That being said...
When I built a test Excel sheet to answer your question, it worked
perfectly. Here's what I did:
Column A: This is an input cell, where you would type the amount of
money per payment
Column B: This is the dropdown validation list, referencing the array
that I named "Period" (this array was located in cells E1:E4)
Column C: This cell contains the formula that calculates the monthly
payment amount; it multiplies Column A by the number of payments,
based on the selection in Column B.
Let's look at the formula again, so that we can see precisely what is
going on (that way you can modify it as needed):
=A2*(CHOOSE(MATCH(B2,Period,0),30,4,2,1))
First, let's look at: MATCH(B2,Period,0)
This finds an exact match for the current value in cell B2, looking in
the array named "Period" (cells E1:E4 in this example). It returns a
number from 1 to 4, based on which Row the value B2 is found in the
array Position.
Now, let's say MATCH returns the number 2 because you chose "Weekly"
in your drop-down list (and "Weekly" was in cell E2).
That leaves us with: =A2*(CHOOSE(2,30,4,2,1))
The way the CHOOSE function works is, the first number in the ( ) is
the reference, followed by a list of possibilities. Those
possibilities have been hard-coded to 30 (as in 30 days per month), 4
(as in 4 weeks per month), 2 (as in 2 bi-weekly periods per month),
and 1 (as in one payment per month).
CHOOSE takes the value of the first, second, third, or fourth item in
the list of possibilities, based on the reference that is calculated
by the MATCH function (1 thru 4).
Hopefully, by understanding the logic, you can adapt this formula to
your needs. Let me know if it works!