Calculating unearned amount billed 45 days in advance...

[HeatherK]

HELP! Excel beginner....exported spreadsheet from Access to Excel. I have a
column with a renewal date for each customer (billing based on the renewal
date, bills 45 days in advance), a column with the amount billed, and a
column with frequency of billing (1-annually billed, 4-quarterly, 12-monthly)
EXAMPLE:
CUSTOMER RENEWAL DATE AMOUNT FREQUENCY
abc company 7/15/07 $1,950.00 4

so, they are billed 6/1, 9/1, 12/1, and 3/1 (as of 6/1 billing, the entire
amount is "unearned")

Need a spreadsheet to calculate how much is unearned at any given time.

Any suggestions? Does this even make sense to anyone? Any help is MUCH
appreciated.

 

[J Sedoff]

I'm unsure how you determine your billing dates (as in, do they update
automatically? Currently, I can't think of a way that you can manage your
billing dates so that they change given what today's date is, if I am
understanding part of your question..), but anyway

You'll need an IF function as a new column, Column F, (ex. for the entry in
row 4):
=IF(E4<B4,"unearned","paid")
where Column E has your billing dates for each customer, and Column B has
your renewal dates.

At the bottom, or top (wherever you feel like putting it), to calculate the
total unearned money, use the equation:
=SUMIF(F4:F5,"unearned",C4:C5)
Column F would contain the IF condition above and Column C is your Amount
Column.

I hope this helps (there is a way to merge the SumIf with the IF formula
above, but I can't remember it right now, sorry), Jim