Calculating unearned amount billed 45 days in advance...

  • Thread starter Thread starter HeatherK
  • Start date Start date
H

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.
 
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
 
Back
Top