Excel Budget Formula

**ExcelNewby** · 10-30-2007, 09:10 PM

I need a formula that will enable me to make an estimate.

Senario: Currently my budget has a total summary of expenses (salary, supplies, software, travel, etc...) for an entire department. I have a group of people who handle special projects off site that are lumped into the total summary of expenses. I need a formula that will give me an approximate estimate of the summary total for the special project group as it fluctuate in number. It cannot be an equal or average distribution or percentage. Can this be done???

Thank you so much!!!!

Desperate yours

**Paul** · 10-30-2007, 10:00 PM

Hello ExcelNewby,

I'm almost certain you're going to have to provide an example workbook for us to help you. You must zip excel files before uploading them.

**MickeyGreen** · 10-31-2007, 11:29 AM

If you can define the algorithm (or at least what it needs to accomplish) it can certainly be done.

**ExcelNewby** · 10-31-2007, 10:43 PM

paul- did you get my previous message?

**ExcelNewby** · 10-31-2007, 11:01 PM

Please see original message for a quick overview. I am trying to determine what proportion of the budget is allocated to the special projects group. The only one that I can accurately identify is the travel expense all others accounts can be estimated using a mathematic average. I've named the cells in the C column with range defined names to reflect the accounts. Asssuming that the department consists of 10 staff and out these 10 four of them are members of the special projects group. Can the formula goes something like this? and how can I incoporate the travel expense that locates on a different tab?

=SUM[(Salaries_Benefits,Other_Personnel,Materials_Supplies,Equipment..etc)/10)*4)]=[????lookup??travel]

Desperately yours,

p.s. Mickey Green-Thank you for your suggestion except I don't know what an algorithym is..i'm computer challenged in every way.

**Paul** · 11-01-2007, 12:17 AM

Hey again,

After looking at your sheet I can somewhat see what you're trying to do, but still have a question.

In the example sheet, if you used the formula

=SUM(C4:C12)*0.4

that will give you the total, based on average, of 4 people's monthly expenses. Now, to add the additional travel expenses from your other sheet.. is the cell that contains that value always the same cell? Or can you name that cell so it can be used in a formula? If cells are inserted or deleted around the named cell it will retain it's name no matter where it moves. Then, you could use:

=SUM(C4:C12)*0.4+MySpecialCellName

If you need to lookup or find that value on the sheet you would need some kind of starting point, and a rule that the cell you're looking for is found in a certain spot (e.g. 6 columns down and 1 right of the cell that says "Special Project Travel"..)

Does that help?