How to calculate X-axis value when Y axis = 0

[lalakis1]

Hello,

I have a set of values on X-axis going like this: 100, 200, 300, etc. Then i have values on Y-axis starting negative and rising above zero like: -142, -35, +12, +132 etc.
How can i have a cell that calculates the value of X-axis when y crosses zero?

Thank you very much!

[dilipandey]

Hi lalakis1,

Do you have any equation which establishes relation between x and y ?

Regards,
DILIPandey

<click on below 'star' if this helps>

[ByronVW]

Dili is right. Depending on the relationship, linear/nonlinear, straight-line/exponential/power etc you cannot work this out. Depending on your equation you use depends on the value. You need to ascertain what behaviors your data exhibits and choose an appropriate model to predict x when y = 0

[lalakis1]

Hi DILIPandley,

Please let me explain. The values on X are the client deposit. The values on Y are the client net profit. The gross client profit is (deposit X performance%), e.g. 550 x 10%. From the gross profit we deduct the service cost, and we get the values of y-axis (client net profit). What i am trying to do i calculate the deposit amount required for a client just to break-even after any service costs, for a specific performance value i input somewhere (the above calculations are based on the performance rate).

In my sheet:

Axis X: Cells A4:A103 contain the deposit amounts (100, 200, 300 etc)
Axis Y: Cells M4:M103 contain the net client profit automatically calculated like this:

(formulas copied down between cells 04 to 103 in all cases):

(Net profit) M4 = E4-J4 (gross profit - total cost)
based on these:
(gross profit) E4 = A4*D$3/100 (deposit X performance % variable defined in D$3)
(total cost) J4 = B4+H4 (service cost + performance fee), where H4 = 25% (if positive, else = 0)

My sheet calculates (for a give performance %), what the net client profit is per deposit level (e.g. for deposit 100 the net profit = e.g. -135, for deposit 200 the net profit = e.g. -42, the for deposit 300 the net profit is positive, e.g. = +102 etc.

What i need is a cell that tells me: the client need to deposit e.g. 278 in order to break even (net profit y=0).

I would be grateful if you could provide the calculation! Thank you so much

[lalakis1]

Is my explanation clear, or would you need any further info?

[lalakis1]

We could say that M4 = (A4*D$3/100) - (B4+(IF((A4*D$3/100)*D$9/100<0,0,(A4*D$3/100)*D$9/100))) (the red part for making negative performance fees equal zero).

This formula copied down gives us all "net profit values". The value of X-axis is shown as A4 in the formula (it is the client deposit amount).

The above formula works and gives me the value of M4. Now, how to calculate what A4 is need for M4 to be zero?

[ByronVW]

Upload your document and lets have a look