+ Reply to Thread
Results 1 to 7 of 7

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

  1. #1
    Registered User
    Join Date
    11-24-2012
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2010
    Posts
    17

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

    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!

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

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

    Hi lalakis1,

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

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

  3. #3
    Registered User
    Join Date
    11-27-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    20

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

    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

  4. #4
    Registered User
    Join Date
    11-24-2012
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2010
    Posts
    17

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

    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

  5. #5
    Registered User
    Join Date
    11-24-2012
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2010
    Posts
    17

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

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

  6. #6
    Registered User
    Join Date
    11-24-2012
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2010
    Posts
    17

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

    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?

  7. #7
    Registered User
    Join Date
    11-27-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    20

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

    Upload your document and lets have a look

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1