View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.misc
curiousgeorge curiousgeorge is offline
external usenet poster
 
Posts: 6
Default DROP DOWN MENUS with formulas

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


"Shonzi" wrote:

On Apr 12, 6:54 pm, curiousgeorge
wrote:
I am trying to create an easy budget. I have a drop down menu with pay
periods or bill payments as daily, weekly, bi-weekly, monthly etc. These are
all in a drop down menu, but now I need to link the chosen drop down menu
period to calculate everything into a monthly payment amount.
I have tried "vlookup" but without success, maybe I don't understand it
enough, but is any way to link a drop down menu choice to a formula?

Thanks


Let's say you have your dollar amount in A2, and your dropdown list of
pay periods in B2, and you want A2*(the value of B2) to appear in
C2... (For the sake of solving the problem at hand, we will assume for
now that every month has 30 days, or 4 weeks.)

You need to assign a "# of days" value to each pay period. For
example, "daily" = 30 because if you pay something daily, you will pay
it 30 times in a month. Thus, A2*30 would give you the correct result.
The value "weekly" would be 4; "bi-weekly" would be 2; and "monthly"
would be 1.

So, the array that you are pulling your dropdown list data from, which
contains "daily", "weekly", "bi-weekly", and "monthly" should be given
a name. (Insert Name Define...) We'll call it "Period".

The formula in C2 should be: =A2*(CHOOSE(INDEX(MATCH(B2,Period,0),1),
30,4,2,1))

MATCH finds the value of B2 in the list "Period"; INDEX turns that
into a number (1 thru 4); and CHOOSE picks the appropriate number
(30,4,2,1) based on that INDEX number. Hope that makes sense!