Budget spreadsheet by Qtrs and %s

[skylinekiller]

I am currently working on a simple budget and need help with percentages and quarter formulas. In cell B8 which is the cell next to Quarter 1, I would like this to add the total amount of ORF events that occurred in Quarter 1. I believe this would be a COUNT type of formula, but I do not know how to make it count all within a Quarter.

I would like cell B9 to display the total amount Spent in Quarter 1.
And Cell B10 would show total dollar amount

I am not set on this format, in fact, I find it a big fat clutter, but don't have many ideas how to design it.


Thank you.

[loginjmor]

Hi -

I think I understand what you want. To sum the number of events in a quarter, we will use SUMPRODUCT and the MONTH functions. We will just have it count the number of times the conditions are true. For second quarter, the formula will look like this:

=SUMPRODUCT(--(MONTH($E$3:$E$5)>3),--(MONTH($E$3:$E$5)<=6))

To add up the actual costs within a quarter, we will just modify the above expression to add in the Actual costs (in column H) for each event that is within the second quarter:

=SUMPRODUCT(--(MONTH($E$3:$E$5)>3),--(MONTH($E$3:$E$5)<=6)*$H$3:$H$5)

Finally, to determine the remainder from budget, we will take the above expression and subtract it from whatever is remaining from the previous quarter (in this case, Q1, which is located in cell B10):

=$B$10-SUMPRODUCT(--(MONTH($E$3:$E$5)>3),--(MONTH($E$3:$E$5)<=6)*$H$3:$H$5)

I have attached a copy of your file with the above formulas.

Hope this helps.

[skylinekiller]

Thank you very much, that was exactly correct. It does what I wanted it to do for the most part with a few exceptions:
1) When I added another ORF it didn't increase the count in the "Quarter" it is still stuck at 1
2) I don't think the Quarters are reflecting properly
1st quarter: 1 October – 31 December
2nd quarter: 1 January – 31 March
3rd quarter: 1 April – 30 June
4th quarter: 1 July – 30 September

2)I have my dates formatted dd-Mmm-yy, why is it not displaying correctly?

Thank you

[loginjmor]

Hi -

1. Make sure when you add quarters that the range in the formula includes the data for the new quarter. So, for example, if you added information on Row 6, you need to change the row references in the formula to go all the way down to row 6. Right now, they stop at Row 5. It's probably easier to insert a row within your list rather than add to the bottom of the list because Excel will automatically change all the range references.

2. I was assuming calendar quarters, not fiscal quarters. So simply move the formulas to the appropriate quarters. For example, calendar quarter 1 is fiscal quarter 2. So move the formulas in cells B9, B10, and B11 to cells B13, B14, and B15. Repeat this process for each of the remaining quarters.

3. Since I am in the US, my worksheet format is MM-DD-YY. It should be a simple matter of highlighting the range and changing the format back to your local format.

[skylinekiller]

I made the changes and all seems well except for my % budget and % Remaining placed data in the cells below when i extended the range. Please see the attachment.
I also changed the style a bit. It seems to be a little more readable this way.

[loginjmor]

Hi -

I agree the new format is better. Modify the % Budget formula in cell H3 to this:

=IF(ISBLANK(A3),"",G3/$D$20)

Copy that down to the bottom of your range. This will just shown nothing if there is no ORF # in Column A.

Same for the remaining column. Change the formula in Cell I3 to look like this:

=IF(ISBLANK(A3),"",$D$20-SUM(G$3:G3))

and copy that down.

Now I noticed Column I is titled % Remaining, but it is actually calculating the value remaining. Is that what you want? If so, you should change the column title. If you actually want % Remaining, simply change the formula I3 to look like this:

=IF(ISBLANK(A3),"",1-SUM(H$3:H3))

Note you will have to change the format to % if you use the above formula.

Hope this helps.

[loginjmor]

Hi -

One more thing. I was just checking the Quarter 2 formula and found the MONTH() function returns a 1 even if the cell is blank. So, to fix that, please replace the formula in cell E15 with the following:

=SUMPRODUCT(--(MONTH($D$3:$D$14)<=3),--(D3:D14<>""))

You will also need to replace the formula in E16 with:

=SUMPRODUCT(--(MONTH($D$3:$D$14)<=3),--(D4:D15<>"")*$G$3:$G$14)

and E17 with

=$B$17-SUMPRODUCT(--(MONTH($D$3:$D$14)<=3),--(D5:D16<>"")*$G$3:$G$14)

[loginjmor]

Thanks for the bump! Please mark the thread as SOLVED if you are satisfied with the result.

[skylinekiller]

I made the changes and it looks good so far. I will fully populate and report back.. Thank you again. You really saved me from a lot of mistakes. I always mess up nested formulas

[skylinekiller]

Hi -

One more thing. I was just checking the Quarter 2 formula and found the MONTH() function returns a 1 even if the cell is blank. So, to fix that, please replace the formula in cell E15 with the following:

=SUMPRODUCT(--(MONTH($D$3:$D$14)<=3),--(D3:D14<>""))

You will also need to replace the formula in E16 with:

=SUMPRODUCT(--(MONTH($D$3:$D$14)<=3),--(D4:D15<>"")*$G$3:$G$14)

and E17 with

=$B$17-SUMPRODUCT(--(MONTH($D$3:$D$14)<=3),--(D5:D16<>"")*$G$3:$G$14)

Would it be possible to double check my total row... Row 16..they aren't matching upcwith quarters.. The quantity row seems to be in order, but i cant get the total row to work.. Quarte 4 total should be quarte 1 total but i couldn't fix it..

[loginjmor]

Hi -

Check in the Total row (row 16) to make sure the month ranges are the same as the formulas in row 15. For example, Quarter 1 should be Months > 9 and <= 12. Also, make sure that follows through in Row 17 as well so you are calculating the correct remaining amount. If you are still having trouble, upload you worksheet and I will take a look at it.