Creating formulas for cells based on dropdown box selection

I'm in the process of elaborating our budget and listing our expenses.

The budget columns are as follows:

Column A: The bill/expense (i.e.: car payment, mortgage, utilities, etc.)
Column B: The amount
Column C: The frequency of payment (i.e.: weekly, monthly or yearly) *DROPDOWN LIST*
Column D: conversion of column B to show the amount per week
Column E: conversion of column B to show the amount per month
Column F: conversion of column B to show the amount per year

Columns D,E & F are my issue. I would like them to take the amount in column B (of the same row) and compute to convert that amount into weekly, monthly and yearly amounts depending on the selection from the dropdown menu.

I need to somehow create the following 3 IF, THEN scenarios:

#1:

IF wk. is selected from the dropdown in column C,

THEN:

- Column D = Column B

- Column E = (columnB*52)/12

- Column F = columnB*52

#2:

IF mo. is selected from the dropdown in column C,

THEN:

- Column D = (columnB*12)/52

- Column E = Column B

- Column F = columnB*12

#3:

IF yr. is selected from the dropdown in column C,

THEN:

- Column D = columnB/52

- Column E = ColumnB/12

- Column F = column B

I created a dropdown box list on sheet 1 (my budget is on sheet 2) with the following selections for the frequency of payment:

• (blank cell)
• wk.
• mo.
• yr.

... but I don't know how to do what I have mentioned above. All I find online is how to do a VLOOKUP and have another word input into a cell, not take a number and apply a formula.

I have attached a screen capture of my budget spreadsheet

Thanks for all your help

Hi, welcome to the forum

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff)

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

(your pic did not come through anyway)

Your formula, based on yoyr description above, would look something like this...

=IF(C2="wk.",B2,if(C2="mo.",B2*12/52,B2/52))
or steamlined...

=B2*IF(C2="wk.",1,if(C2="mo.",12/52,1/52))

You should be able to adapt this for the other cells, if not, let me know

Here's the .xlsx workbook

Budget.xlsx

Are you trying to calc how much you would pay over a year, based on that amount and whether the payments aweekly, monthly or yearly?

What would some expected answers look like?

divisible assets.

Take the car payment for example @ $370/mo.

After inputting 370 in B4 and selecting mo. in C4, I would expect the following results:

• D4=$85.38
• E4=$370.00
• F4=$4,440.00

Does that make sense?

=$B4*IF($C4="wk.",1,IF($C4="mo.",12/52,1/52))
=$B4*IF($C4="wk.",12/52,IF($C4="mo.",1,1/12))
=$B4*IF($C4="wk.",52,IF($C4="mo.",12,1))
It would make it simpler if you had a hidden row with the factors in them

Originally Posted by FDibbins

It would make it simpler if you had a hidden row with the factors in them

That's probably exactly what I'm looking for, because one cell can have 3 different formulas depending on the selection from the dropbox.

How would I create a hidden row with those factors?

hmm ok, different approach.

I changed your drop-downs to match your headings (not essential, purely preference)
Then I added row headings to match in Lists C2:C4 and changed the entries inside the table to be the actual conversion values.

You now have a table you can use for INDEX/MATCH combo...
=IF($B5="","",$B5*INDEX(lists!$D$2:$F$4,MATCH($C5,lists!$C$2:$C$4,0),MATCH(D$4,lists!$D$1:$F$1,0)))

See attached

Originally Posted by FDibbins

hmm ok, different approach.

I changed your drop-downs to match your headings (not essential, purely preference)
Then I added row headings to match in Lists C2:C4 and changed the entries inside the table to be the actual conversion values.

You now have a table you can use for INDEX/MATCH combo...
=IF($B5="","",$B5*INDEX(lists!$D$2:$F$4,MATCH($C5,lists!$C$2:$C$4,0),MATCH(D$4,lists!$D$1:$F$1,0)))

See attached

You Sir, are a lifesaver!!! I only modded the numbers in the list because I noticed that some of the calculations were off (I wish my mortgage was $4,26/yr. :P)

Would you care to explain the ''meaning'' of the formula you inserted in the cells so I can apply this method to other projects?

btw, is there a hidden row as you had previously mentioned?

There is no hidden row, I decided to go the INDEX/MATCH route instead.

That formula I used...
=IF($B5="","",$B5*INDEX(lists!$D$2:$F$4,MATCH($C5,lists!$C$2:$C$4,0),MATCH(D$4,lists!$D$1:$F$1,0)))
1st part...=IF($B5="","", is just used to error-trap so you can copy it down past where you have data (so you can grow the table). If there is no data in column B of that row, the cell with the formula in it will show as blank

For the rest...
$B5*INDEX(lists!$D$2:$F$4,MATCH($C5,lists!$C$2:$C$4,0),MATCH(D$4,lists!$D$1:$F$1,0))
I am using the INDEX/MATCH to ID the factor needed, and then applying that to whateverthe value in B is.

Index returns the contents at the intersection of a specified row and column, the syntax is =INDEX(range,row number,column number)

To find the row and column, I used 2 MATCH() functions...
MATCH finds which row (or column) a specified value is, in a given range, the syntax is =MATCH(find what, column to search down, 0) 0 is for an exact match. This version finds the row number, that would change to =MATCH(find what, row to search across, 0) to find the column number
MATCH($C5,lists!$C$2:$C$4,0) to find the row
MATCH(D$4,lists!$D$1:$F$1,0) to find the column

So we then plug the 2 MATCH's into the INDEX to give the factor

Hope that helped, if not, shout again, and I will try to explain differently

Originally Posted by FDibbins

That formula I used...
=IF($B5="","",$B5*INDEX(lists!$D$2:$F$4,MATCH($C5,lists!$C$2:$C$4,0),MATCH(D$4,lists!$D$1:$F$1,0)))
1st part...=IF($B5="","", is just used to error-trap so you can copy it down past where you have data (so you can grow the table). If there is no data in column B of that row, the cell with the formula in it will show as blank

For the rest...
$B5*INDEX(lists!$D$2:$F$4,MATCH($C5,lists!$C$2:$C$4,0),MATCH(D$4,lists!$D$1:$F$1,0))
I am using the INDEX/MATCH to ID the factor needed, and then applying that to whateverthe value in B is.

Index returns the contents at the intersection of a specified row and column, the syntax is =INDEX(range,row number,column number)

To find the row and column, I used 2 MATCH() functions...
MATCH finds which row (or column) a specified value is, in a given range, the syntax is =MATCH(find what, column to search down, 0) 0 is for an exact match. This version finds the row number, that would change to =MATCH(find what, row to search across, 0) to find the column number
MATCH($C5,lists!$C$2:$C$4,0) to find the row
MATCH(D$4,lists!$D$1:$F$1,0) to find the column

So we then plug the 2 MATCH's into the INDEX to give the factor

Hope that helped, if not, shout again, and I will try to explain differently

Thank you so much for your help!!!

I added an extra entry in the dropdown menu and also created another dropdown menu to be able to show which account the payment is coming from.

Would I used the same type of formula to simultaneously, yet individually, sum up the 3 converted columns (weekly, monthly & yearly) in a separate table depending on the account selected?

For example, if I choose to see how much comes out of the JOINT account, in the next 3 cells, it would add up the weekly, monthly and yearly totals of the JOINT account only. Then if I chose to see how much comes out of the CHQ account, I choose CHQ from the dropdown and the weekly, monthly and yearly totals are calculated only for the items which payments are coming out of the CHQ account. Am I clear?

If column D is equal to D33, then E33 is the SUM of column E matching the value of D33 in column D
If column D is equal to D33, then F33 is the SUM of column F matching the value of D33 in column D
If column D is equal to D33, then G33 is the SUM of column G matching the value of D33 in column D

I have attached the updated budget.

BUDGETef.xlsx