Excel help with formula please.

[Equine Investor]

Hi, I am having trouble with a formula:
Here are the references:
J6=Stake (dollar amount)
J7=Commision (percentage)
I3 is either blank or dollar amount.
If I input this formula into I3 which is not blank, it works fine with $1.00 stake, but if I change the dollar amount in J6, it doesn't calculate correctly.
Here's my formula currently in I3:
=IF(I3="",$J$6*(100%-$J$7),(-I3-$J$6)+$J$6*2)
Any help appreciated.

 

[Joe4]

I3 is either blank or dollar amount.
If I input this formula into I3 which is not blank, it works fine with $1.00 stake, but if I change the dollar amount in J6, it doesn't calculate correctly.
Here's my formula currently in I3:
=IF(I3="",$J$6*(100%-$J$7),(-I3-$J$6)+$J$6*2)

Are you REALLY trying to put a formula in cell I3 that contains references to cell I3 itself?
That will create circular references (a formula that is calling itself, the cell it resides in).
Is that really what you are trying to do, or is that a typo?

If that is really what you are trying to do, please show us a few examples with actual values and what your expected output is.

 

[Equine Investor]

Yes, it was a typo sorry, the formula is pasted into:
Here's my formula currently in J13:
=IF(I13="",$J$6*(100%-$J$7),(-I13-$J$6)+$J$6*2)

 

[felixstraube]

It seams to work correctly:

In row 13: I13 is "" so you will get 15*(100%-5%)=14,25 which is correct.
In row 14: I14 is 10 so you will get -10-15+15*2=5 which is also correct.

Now I have to ask. Why do you do this?: (-I13-$J$6)+$J$6*2
Is there an error? Because that simply would be equal to $J$6-I13

 

[Equine Investor]

Thanks, but in J14, it should be -135
Because liability is -(I14-$J$6)

 

[felixstraube]

You would have to share your real data or workbook and expected results.
Im really not sure what you formula should look like with all the typos...

 

[Joe4]

Thanks, but in J14, it should be -135
Because liability is -(I14-$J$6)

Yeah, you seem to have too many typos in your question to make much sense of it.
Please walk us through your example, showing the original values that your formula is referencing in each cell, and explain what the formula should be returning and why (explain your logic).

 

[Equine Investor]

I figured it out...
The correct formula is =IF(J9="",$K$6*(100%-$K$7),(-J9*$K$6)+($K$6*1))
Thanks to all who tried to help.

 

[Joe4]

I am glad you figured it out.
We might have been able to help you resolve it sooner if you had answered our questions, so we could understand exactly what you were after.

 

[felixstraube]

Basically you calculate the same thing with two different percentages.

(-J9*$K$6)+($K$6*1) is the same as $K$6*(100%-J9)
so your formula would look like:

=IF(J9="", $K$6*(100%-$K$7), $K$6*(100%-J9))

or

=IF(J9="", $K$6*(1-$K$7), $K$6*(1-J9))