Everything looks like a nail!

July 8, 2009 11:44 AM

Asking_for_friendFilter/ExcelFilter: I’ve built a pretty basic calculator in Excel to help people figure out how much to bill for partial months vs. full months. As you can guess, there's...

Is it possible to take a value from one formula result (number of billing periods = 4) and automatically insert rows with values as listed? Is Excel even capable of this? Ideally, this would be a form for internal use only.

Number = 4
Insert 4 rows
Label with date ranges (see example below)
Insert formulas for calculating billing

Example:
Start Date: 08/12/2009
End Date: 11/11/2009
Duration: 3 months
Cost: $1000 per month
Billing Periods: 4

1st Billing Period: 08/12/2009 - 08/31/2009 20 days
2nd Billing Period: 09/01/2009 - 09/30/2009 full month
3rd Billing Period: 10/01/2009 - 10/31/2009 full month
4th Billing Period: 11/01/2009 - 11/11/2009 11 days

Number of Partial Months: 2
Number of Full Months: 2

P.S. - I'm going to try and help him build this in PHP, which should be brain-dead simple, but he'd like the Excel version of the solution too. Thanks!

posted by littlerobothead to computers & internet (2 comments total)

If you actually need to insert new rows you want VB scripting. Mr. Excel has a great scripting message board where you can get detailed help with this.

If billing periods won't realistically be over a certain number you could write a series of formulas into the cells with a few conditionals to step out of the formula unless it's necessary. I.e.:

Assuming the billing period number is in cell A1:

Row 1: =IF(A1>1,1st billing period formula here)
Row 2: =IF(A1>2,2nd billing period formula here)
...etc...
posted by odinsdream at 11:50 AM on July 8

I suggest building a template spreadsheet that would take your variables and populate the appropriate values using formulas only. This is still really advanced Excel work - I can think of a two-step approach that would split out the billing across all months of the year, and then display in the desired area of your spreadsheet only the months you need - but it's easier for the novice to trace formulas back to the source information in cells than to try to make sense of Visual Basic code. And then you could provide an updated template next year or whenever is appropriate.
posted by RobinFiveWords at 12:47 PM on July 8