Forum Discussion

renee22roxy's avatar
renee22roxy
Copper Contributor
Oct 14, 2019

Excel Help

Hi    So I am creating a budget spreadsheet on Excel for myself. I need help with once I select the Drop down list Frequency - (Weekly,Fortnightly,Monthly,Quarterly,Annually) - Have it automatical...
excel
Formulas and Functions
Twifoo's avatar
Twifoo
Silver Contributor
Oct 14, 2019

renee22roxy 

To facilitate testing of formulas, please attach your sample file. Also, please explain how you calculate the amounts for each frequency.

renee22roxy's avatar
renee22roxy
Copper Contributor
Oct 14, 2019

Twifoo 
I have edited the post and attached the sample file. 

 

If you see the document attached - i have a section at the bottom for the formulas, 

e.g. - Fortnightly > to weekly =SUM(B36/2) > to fortnightly =SUM(B36*1) > to monthly =SUM(B36*26/12) > to Annual =SUM(B36*26)

Currently I am copying pasting the section to make it work but would prefer once i click the frequency type it automatically calculates it all. 

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Oct 14, 2019

    renee22roxy 

    In the attached version of your file, the formula in D14, copied down and across the range, is: 

    =$B14*VLOOKUP($C14,
    FrequencyTable,
    MATCH(D$12,FrequencyLabels,0),0)

    Note that I modified the labels in Sheet1!D12:G12 to conform to the FrequencyLabels in Sheet2. 

Resources