excel formula help

[platinumaa]

hello all, newbie here. need a little help with an excel spreadsheet i am making. here is the cell fomulas


- 61% FORMULA.. =SUM(D12+D13+D14+D16+D19+D20+D21+D22)*0.61+D15*0.5-(D38/2)


- 39% FORMULA.. =SUM(D12+D13+D14+D16+D19+D20+D21+D22)*0.39+D15*0.5-(D38/2)



the end result are the fees to be split up 61% and 39% for lines D12-D22 ($25,000 line), than line D15 (which is the $10,000 construction admin line) is split up 50%. line D38 ($27,000 line) is a subtraction from D12-22.. but the formulas above arent working out that way. im thinking its an order of operations issue

[/URL][/IMG]




i appreciate the responses / help!

 

[kweaver]

Difficult to tell what's in what cell. Where (what cells) are the contract amounts, total fees, and expenses?

 

[platinumaa]

here is a screenshot of the excel cell info

[/URL][/IMG]

 

[Dave Patton]

N.B. Use the appropriate cell references instead of named ranges for "Total fees"
and "Total Expenses"

Excel 2010
	B	C	D	E	F
1
2	Total fees		27,000.00	15,370.00	11,630.00
3				15,370.00	11,630.00
4
5	or assuming expenses are shared 50% and 50%
6
7	Total fees - expenses		25,500.00	14,620.00	10,880.00
8				14,620.00	10,880.00
2d

Cell Formulas
Range	Formula
E2	=(Total_fees-D18)*0.61+D18*0.5
E3	=(27000-10000)*0.61+10000*0.5
E7	=(Total_fees-D18)*0.61+(D18-rExpenses)*0.5
E8	=(Total_fees-D18)*0.61+(D18-rExpenses)*0.5
F2	=(Total_fees-D18)*0.39+D18*0.5
F3	=(27000-10000)*0.39+10000*0.5
F7	=(Total_fees-D18)*0.39+(D18-rExpenses)*0.5
F8	=(27000-10000)*0.39+(10000-1500)*0.5
D7	=Total_fees-1500

Named Ranges
Name	Refers To	Cells
rExpenses	='2d'!$D$19
Total_fees	='2d'!$D$2

 

[kweaver]

The 39% formula in C45 is taking 39% of the sum. That would be 39% of 17,000 which is 6,630.
Then 6630 is added to half of the Const Admin (half of 10,000 or 5,000) = 11,630
Then, half of 1,500 (or 750) is subtracted from the result, ending with 10800, as you show.

But, that's 39.5636% of 27500. 14,620 is only 53.1636% of 27500.

 

[platinumaa]

wow, thats a lot for me, im kind of a novice. will have to study what you have above to better understand and figure out how to implement into my worksheeet

thank you!!

 

[platinumaa]

it looks like that is my issue, where i went wrong

 

[platinumaa]

Dave Patton.. unless im reading what you posted incorrectly.. are you saying my spreadsheet is accurate? i got the same end result numbers.. $14620 and $10880. what am i missing?

thanks!