Adding in intervals

Microsoft Excel home and student 2010 so...
December 28, 2010 at 14:28:29
Specs: Windows 7
How do I create a formula in Excel (2010) to add a cell’s value at different intervals? I have a sheet built with a date range broken-down by month over a two year period (C1:Z1). Each row represents fees charged (A2:A20) and their respective billing intervals; quarter, bi-annually or annually (B2:B20). Example, Add $10 Fee (A2) every quarter (B2) illustrated over a two year period (C2:Z2).

See More: Adding in intervals

Report •

December 28, 2010 at 17:14:36
Please read the How To in my signature line and post a short example of your spreadsheet layout, including one or two examples of "output".

That will give us a better understanding of what you have and prevent us from working on something that won't match your needs.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

December 28, 2010 at 20:16:52
I appreciate any assistance you can provide. There are multiple sheets in my spreadsheet. One of the sheets creates a "Fee Schedule" which outlines a "Description", "Fee" and Interval of the charges.
Description	Fee	Interval
Reporting Fee:	$25.00 	Bi-Annual
Hosting Fee:	$15.00 	Annual
Storage Fee:	$10.00 	Quarterly

Another sheet illustrates the results of the "Fee Schedule" over a period of 2 years.
Jan-10	Feb-10	Mar-10	Apr-10	May-10	Jun-10	Jul-10	Aug-10	Sep-10	Oct-10	Nov-10	Dec-10	Jan-11
$25.00 	$0.00 	$0.00 	$0.00 	$0.00 	$25.00 	$0.00 	$0.00 	$0.00 	$0.00 	$0.00 	$0.00 	$25.00 
$15.00 	$0.00 	$0.00 	$0.00 	$0.00 	$0.00 	$0.00 	$0.00 	$0.00 	$0.00 	$0.00 	$0.00 	$15.00 
$10.00 	$0.00 	$0.00 	$10.00 	$0.00 	$0.00 	$10.00 	$0.00 	$0.00 	$10.00 	$0.00 	$0.00 	$10.00 

I'm not sure if I have followed your directions correctly. I really need the help and appreciate your time. Thanks.

Report •

December 29, 2010 at 07:48:31
I'm still a little confused on your layout.

In your OP you gave specific columns and rows (e.g. A2:A20 for the fees and B2:B20 for the intervals) yet the Fee Schedule table in your second post seems to take up 3 columns (and it's on a different sheet?)

In addition, you said your dates start in C1 but since you didn't include Row numbers or Column letters I don't know if that's still the case.

Please clarify this.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

December 29, 2010 at 17:44:27
Sorry for the confusion. In my OP I was attempting to streamline the question and obviously did a poor job of it. Let me take a stab at clarifying my situation.

I have created a workbook which I am using as a pro forma to demonstrate fees charged over a 2 year period of time. The workbook contains many sheets. There are 4 different sheets outlining different fees (“Fee Schedule”) and a 5th sheet (“Summary”) illustrating a 2 year projection of the total fees derived from the “Fee Schedules”. Below is an example of the format/layout of a “Fee Schedule” sheet:

	A	        B	        C
1	Description:	Fee Amount:	Interval Charged:
2	Reporting Fee:	$25.00 	        Bi-Annual
3	Hosting Fee:	$15.00 	        Annual
4	Storage Fee:	$10.00 	        Quarterly
5	Data Fee:	$5.00 	        Monthly

The “Fee Schedule” sheets incorporate many different fee types (i.e. Reporting Fee, Hosting Fee, Storage Fees, etc.). These fees are all charged based on varying intervals (i.e. monthly, quarterly, bi-annually and annually). I need to illustrate those fees and their respective intervals on the “Summary” sheet. I would like to be able to enter QUARTERLY in the interval field within the “Fee Schedule” and have it automatically add the corresponding fee to the “Summary” sheet based on quarterly intervals. Below is an example of the format/layout of the “Summary” sheet:
	A	        B	C	D	E	F	G	H	I	J	K	L	M	N
1	Description	10-Jan	10-Feb	10-Mar	10-Apr	10-May	10-Jun	10-Jul	10-Aug	10-Sep	10-Oct	10-Nov	10-Dec	11-Jan
2	Reporting Fee:	$25.00 	$0.00 	$0.00 	$0.00 	$0.00 	$25.00 	$0.00 	$0.00 	$0.00 	$0.00 	$0.00 	$0.00 	$25.00 
3	Hosting Fee:	$15.00 	$0.00 	$0.00 	$0.00 	$0.00 	$0.00 	$0.00 	$0.00 	$0.00 	$0.00 	$0.00 	$0.00 	$15.00 
4	Storage Fee:	$10.00 	$0.00 	$0.00 	$10.00 	$0.00 	$0.00 	$10.00 	$0.00 	$0.00 	$10.00 	$0.00 	$0.00 	$10.00 
5	Data Fee:	$5.00 	$5.00 	$5.00 	$5.00 	$5.00 	$5.00 	$5.00 	$5.00 	$5.00 	$5.00 	$5.00 	$5.00 	$5.00 
6	Total Fees:	$55.00 	$5.00 	$5.00 	$15.00 	$5.00 	$30.00 	$15.00 	$5.00 	$5.00 	$15.00 	$5.00 	$5.00 	$55.00 

I have already incorporated the monthly fees (the easy part) and now I need to incorporate the other intervals and have now hit this roadblock. I hope this explanation made more sense and did not confuse the matter further. Please let me know and again thanks for your help.

Report •

December 29, 2010 at 18:39:28
Shouldn't your Bi-Annual Reporting Fee be charged in January and July, not January and June? For the purposes of this exercise, I'm gong to assume it should be.

This example is based on using the Fee Schedule in Sheet1!A1:C5.

In the Summary sheet, with the dates starting in B1 and the Descriptions in Column A, as in your example, I entered the following formula in B2 and dragged it across and down.

What it does is lookup the "Interval" in the Fee Schedule table and the month in Row 1 and place the fee in each cell if the criteria fit.


BTW, regarding your statement: I would like to be able to enter QUARTERLY in the interval field...

I suggest you use Data Validation Drop-Downs to eliminate the possibility of typos which would cause the formula to return 0.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

December 30, 2010 at 20:14:48
I followed your example and incorporated your formula into my spreadsheet and it worked correctly! I also incorporated your Data Validation Drop-Downs suggestion and it is perfect.

DerbyDad03 you are the best. I have been trying to figure this problem out for a LONG time and you solved it for me in an hour. I really appreciate your time and effort. I hope someone does something really nice for you too. I wish you and your family a happy, healthy and prosperous new year. Thank you very much.

Report •

December 30, 2010 at 20:48:07
I'm glad I could help.

The same Holiday Greetings to you and yours.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Ask Question