highest possible number from sum between number and porcentage?

[colonel179]

Let's say I have a budget of $5000. I get 20% of what I spend without going over the budget. Example: I spend $1200, so I get 20% of that: $240.

However, the money spent + 20% of that cannot exceed the budget.

How can I do that automatically in excel? is there a way i can type $5000, and with a formula, I automatically get the most I can spend (+ 20%)?

In this example, the most I can spend without going over adding both is: Spent ($4166.6) + 20% ($833.32) = $4999.92 (it does NOT go overbudget), that means that maximum I can spend is $4166.6 and the max I can get from 20% is $833.32.

I would like to know if there is a formula that could give me either the max money I can spend, or max 20% I can get ( or both) automatically. Is it possible?

Thank you very much!

[mel0nc0lly]

This would work, and give you both values in one cell.

="Spend - " & TEXT(H8/1.2,"0.0") & ", you get " & TEXT((H8/1.2)*0.2,"0.0")

Where H8 contains your budget number

Not very helpful if you then need to do something with the results, but at at least you'll have your answers.

[shg]

Wouldn't it make more sense that you get 20% of the UNSPENT budget?

So if you accomplish a task with a budget of $5000 for $1000, you get $800. If you spend $4000, you get $200, and if you spend $5000 (or more), you get nothing.

[MrShorty]

How much is Excel and how much is algebra? This should be a simple algebra problem. Once the algebra is done, the programming should also be simple. let x be budget spent. According to your description, the problem should be x+0.2x=5000 and solve for x. Does that look correct?

[colonel179]

This would work, and give you both values in one cell.

="Spend - " & TEXT(H8/1.2,"0.0") & ", you get " & TEXT((H8/1.2)*0.2,"0.0")

Where H8 contains your budget number

Not very helpful if you then need to do something with the results, but at at least you'll have your answers.

I don't actually have to do anything with it, only display it. I guess I could try your solution. Thanks for helping!

Wouldn't it make more sense that you get 20% of the UNSPENT budget?

So if you accomplish a task with a budget of $5000 for $1000, you get $800. If you spend $4000, you get $200, and if you spend $5000 (or more), you get nothing.

I get the money for trips. I get 20% of what I spend because it's for things that I can't get an invoice for. I don't really know why they do it like that. I have an expenses file in excel, and it would help me a lot if I can show what is my real spending amount, and what I would get as 20%.

How much is Excel and how much is algebra? This should be a simple algebra problem. Once the algebra is done, the programming should also be simple. let x be budget spent. According to your description, the problem should be x+0.2x=5000 and solve for x. Does that look correct?

Yes. You are correct! It's just that I don't know how to translate it to a formula, so that I type 5000 and it gives me the answer right away.

[jtakw]

Hi,

Excel 2016 (Windows) 64 bit

	A	B	C	D
1	Budget	Max to Spend	Your "Cut"	Total Spent plus your Cut
2	5000	4166.666667	833.33333	5000

Sheet: Sheet42

Excel 2016 (Windows) 64 bit

	B	C	D
2	=A2/1.2	=B2*0.2	=B2+C2

Sheet: Sheet42

[colonel179]

Hi,

Excel 2016 (Windows) 64 bit

	A	B	C	D
1	Budget	Max to Spend	Your "Cut"	Total Spent plus your Cut
2	5000	4166.666667	833.33333	5000

Sheet: Sheet42

Excel 2016 (Windows) 64 bit

	B	C	D
2	=A2/1.2	=B2*0.2	=B2+C2

Sheet: Sheet42

Thank You very much! i adapted this and I had ot exactly how I wanted.