Profit Formula

[robvpp]

I'm trying to Total a Single Product Profit: =((Cost+11% (Overhead)+Transaction Cost (default to 5, if not higher than 5))*25% (Taxes) ))*40%(Profit))

5 being a minimum of 5 Dollars. Here is the Non-Excel Formula I came up with that isn't working:

Non-Excel Formula: =((Cost+11% (Overhead)+(Transaction Cost if higher than 5)Minimum 5)*25% (Taxes) ))*40%(Profit))
Excel Example: =(1*(((F9+(F9/0.89-F9))+1*(F9*K6+L6))MAX(5))*25%)/0.6

Obviously, right now it's a mess. Any help would be appreciated.

Thank you in advance.

 

[BigBeachBananas]

Try...

...MAX(5,Transaction Cost)...

 

[robvpp]

Hi BigBeachBananas,

I appreciate you getting back to me. Excel "fixed" it to: =(1*((F10+(F10/0.89-F10))+1*(MAX(5,F10*K6+L6)))*25%)/0.6

Which also isn't working.

robvpp

 

[BigBeachBananas]

Hi BigBeachBananas,

I appreciate you getting back to me. Excel "fixed" it to: =(1*((F10+(F10/0.89-F10))+1*(MAX(5,F10*K6+L6)))*25%)/0.6

Which also isn't working.

robvpp

Can you share sample data?

Excel formula doesn't match your non-excel formula e.g. where is the 11% overhead and 40% profit?

 

[robvpp]

If anyone can still help with this I've been able to better format: =1*((((F15+(F15/0.89-F15))+1*(F15*K6+L6))*MAX5)*25%)/0.6

However, I STILL can't seem to get the MAX5 ($5 Minimum if the logic/formula doesn't factor to that level) to work/function.

Again, thank you in advance for any help out there.

 

[Dave Patton]

Please provide an extract of your sheet with the forum's tool named XL2BB.
Include your expected result.

Does the following help?

T202310a.xlsm
	A	B	C	D	E	F	G	H
1						BeforeT	Taxes	Net Profit
2		3,410.42	1,000.00	150.00	400.00	1,816.42	454.11	1,362.32
3								1,362.32
3g

Cell Formulas
Range	Formula
F2	F2	=Revenue-(Costs+1.11*Overhead+MAX(5,TransactionC))
G2	G2	=F2*0.25
H2	H2	=F2-G2
H3	H3	=0.75*(Revenue-(Costs+1.11*Overhead+MAX(5,TransactionC)))

Named Ranges
Name	Refers To	Cells
Costs	='3g'!$C$2	H3, F2
Overhead	='3g'!$E$2	H3, F2
Revenue	='3g'!$B$2	H3, F2
TransactionC	='3g'!$D$2	H3, F2

 

[Dave Patton]

information?

LET(c,(Costs+1.11*Overhead+MAX(5,TransactionC)),(c/(1-0.532609)-c)*0.75)

 

[robvpp]

Hi Dave,

Sorry for the late reply, I've been moving my Emails to a more secure Server. Ugh... Anyway, I've tried in the past to use/provide the forum's tool named XL2BB, with no luck.

I very much appreciate you providing the above.

In the meantime, I've continued to play around with the formula until I've got it as close as I think I'm going to....EXCEPT for the MAX5. So, please see below for what I have:

Final Formula LESS the $5 Minimum: =(F9+((F9/0.89-F9)+((F9*K6)+L6)+((((F9+(F9/0.89-F9)+((F9*K6)+L6)*0.25)))))/0.6)

Screenshot:

Excel Sheet:

Factors	Cost	(+)	Overhead	(+)	Transaction Cost	Taxes	(+)	Profit	(+)	MAX5 (it doesn’t have to be MAX5, just whatever the formula creates to have the Amount be a Minimum of $5)
	0.029%	$0.30
Cell	B4		N/A		B1	B2	N/A		N/A
Cost	$0.50		N/A				N/A		N/A
Formula:			B4/0.89-B4		(B4*B2)+C2)	(B4+(B4/0.89-B4)+((B4*B2)+C2)*0.25)		(B4+((B4/0.89-B4)+((B4*B2)+C2)+((((B4+(B4/0.89-B4)+((B4*B2)+C2)*0.25)))))/0.6)
			$0.06		$0.30	$0.64		$2.16	$5.00 (Total should be a Minimum of $5)

Again, any help would be greatly appreciated.

Thanks,
Rob

 

[Dave Patton]

Please review post #6.
N.B. You can paste the information to a clean sheet. Click on the icon below the f(x) in the header and move to your sheet and paste.

Please explain your full calculation.
An extract of your sheet will be useful.
Does the formula that I show not show the minimum of 5 or the transaction fee amount whichever is greater.

 

[robvpp]

Hi Dave,

Re: LET(c,(Costs+1.11*Overhead+MAX(5,TransactionC)),(c/(1-0.532609)-c)*0.75)

The MAX would be for the Total of everything before Profit.

Example of the formula I came up with (and, this doesn't work): =((F9+((F9/0.89-F9)+((F9*K6)+L6)+((((F9+(F9/0.89-F9)+((F9*K6)+L6)*0.25)))))*MAX5)/0.6)

I hope that helps clarify?

Thanks again,
Rob