0

I'm (an amateur) and I'm creating a sheet to help me with Budgeting. I've got bills that I can enter, but that come at a variety of different intervals. I'd love it if I could enter the total from the bill into one cell in a row, and have excel workout the amount for all the other intervals (four other cells in the row).

I known how to write formulas into a cell so that the another cell in the row will populate correctly (=sum(B3/52) but don't know how to use 'or' so that the formula knows to look in one of four other cells to find a value to use... would it take VBA to do that?

Id post an example.. but I need a ranking here to do that. Column headings are intervals i.e. Weekly, Monthly, Quarterly, Annually

Im using excel 2010.

Thanks for any Help !

2

1 Answer 1

1

If I understand correctly, you want to enter a bill's amount in one cell which may be a weekly, monthly, quarterly or annual amount and, based on the actual interval of each bill, calculate the amount for all other intervals.

One way to do it is is to use a series of nested IF statements.

Set up your spreadsheet as outlined below:

Label columns A-F as follows:

  • A = Interval
  • B = Amount
  • C = Weekly
  • D = Monthly
  • E = Quarterly
  • F = Annually

In subsequent row, place a code in column A to indicate the actual interval of your bill:

  • W for Weekly
  • M for Monthly
  • Q for Quarterly
  • A for Annually

Place actual bill amount in Column B.

Put formulas below in Columns C through F:

Formula for Cell C2:

=IF(A2="W",B2,(IF(A2="M",B2/4,(IF(A2="Q",B2/12,IF(A2="A",B2/52,""))))))

Formula for Cell D2:

=IF(A2="W",B2*4,(IF(A2="M",B2,(IF(A2="Q",B2/3,IF(A2="A",B2/12,""))))))

Formula for Cell E2:

=IF(A2="W",B2*12,(IF(A2="M",B2*3,IF(A2="Q",B2,IF(A2="A",B2/4,""))))))

Formula for Cell F2:

=IF(A2="W",B2*52,(IF(A2="M",B2*12,(IF(A2="Q",B2*4,IF(A2="A",B2,""))))))

Note that these formulas assume you are using columns A-F. If you are putting this in another part of your spreadsheet, you have to adjust accordingly.

They also assume 4 weeks to a month, 12 weeks to a quarter, 3 months to a quarter, and 52 weeks to a year.

I think I got the calculations right, but it's always possible there's a typo. The logic of the syntax, however, is correct.

Once the formulas are entered in one row, you can copy them to as many other rows as necessary.

1
  • Thanks Elaine & Ben, my Sheet = fantastic! I would never have come up with that. I changed the formula just slightly to make the calculations more accurate... I used this: Weekly=IF(D16="W",C16,(IF(D16="M",C16/30.416*7,(IF(D16="Q",C16/91.25*7,IF(D16="A",C16/52,IF(D16="F",C16/2,""))))))) Fortnightly=IF(D16="W",C16*2,(IF(D16="M",C16/30.416*14,(IF(D16="Q",C16/91.25*14,IF(D16="A",C16/26,IF(D16="F",C16,""))))))) e.c.t. I also learnt careful use of the escape key !! Cheers !
    – Mike
    Nov 18, 2013 at 10:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .