+ Reply to Thread
Results 1 to 12 of 12

Possible Formula to calculate described probabilities

  1. #1
    Registered User
    Join Date
    10-22-2018
    Location
    Turkey
    MS-Off Ver
    Office2010
    Posts
    84

    Question Possible Formula to calculate described probabilities

    Hi to All,

    I explained the courses of calculation in my attached excel sheet and request a possible formula to calculate depending the described probabilities in my sheet.

    Thanks for kindest helps in advance
    Attached Files Attached Files

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Possible Formula to calculate described probabilities

    Misinterpreted your examples.

    A
    B
    C
    D
    E
    F
    G
    H
    I
    3
    Row rate
    Disc-1 (-%)
    Disc-2 (-%)
    Disc-3 (-#)
    Com (-/+%)
    SUM
    4
    100.00
    15.00
    10.00
    5.00
    10.00
    79.44
    79.44
    H4: =(A4 * (1 - B4/100) * (1 - C4/100) - D4) / (1 - E4/100)
    5
    100.00
    15.00
    10.00
    5.00
    -10.00
    64.35
    65.00
    Last edited by shg; 04-04-2019 at 12:34 PM.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    10-22-2018
    Location
    Turkey
    MS-Off Ver
    Office2010
    Posts
    84

    Re: Possible Formula to calculate described probabilities

    Dear Shg,
    I checked my calculation but seems true maybe the way of calculation can be unusual but let me explain here: 1st I want to subtract 15% from 100 then subtract 10% from the sum of 1st operation and subtract 5 (not percentage) from sum of 2nd operation.
    In "Com" row I want to calculate whether plus or minus depending the insertion; in plus mode dividing E4 value (here 10%) to 0.90 if it is 15 dividing into 0.85, etc. will give me the gross total so when I subtract commission of 10% from 79.44 can reach my netto 71.50 which is the last sum after the implementation of all discouns (1-2-3). In minus mode doing the same operation as in discount-1 calculation 71.50-10%= 64.35. Hope define well what I suppose to solve
    Regards

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Possible Formula to calculate described probabilities

    Right. See the formula.

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Possible Formula to calculate described probabilities

    It would be way more intuitive if percentages were formatted as percentages and dollars (lira) as currency:

    A
    B
    C
    D
    E
    F
    G
    H
    I
    3
    Row rate
    Disc-1 (-%)
    Disc-2 (-%)
    Disc-3 (-#)
    Com (-/+%)
    SUM
    4
    100.00 ₺
    15%
    10%
    5.00 ₺
    10%
    79.44
    79.44
    H4: =(A4 * (1 - B4) * (1 - C4) - D4) / (1 - E4)
    5
    100.00 ₺
    15%
    10%
    5.00 ₺
    -10%
    64.35
    65.00

  6. #6
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Possible Formula to calculate described probabilities

    I agree with shg formating would be much better

    =(A4 * (1 - B4/100) * (1 - C4/100) - D4) which is 71.5 we agree on, but if commission is negative multiply by (1+commission%) but if it is positive divide by (1-commission %)

    its not consistent
    =(A4 * (1 - B4/100) * (1 - C4/100) - D4)*IF(E4>0,1/(1-E4/100),1+E4/100)

    I am suspecting the commission is misleading and the + or - depends if it is already included in the calculation, but strange maths!
    Last edited by davsth; 04-04-2019 at 01:28 PM.

  7. #7
    Registered User
    Join Date
    10-22-2018
    Location
    Turkey
    MS-Off Ver
    Office2010
    Posts
    84

    Re: Possible Formula to calculate described probabilities

    Thanks Shg an important missing to clarify my request. Your formula works fine for +10% of comission but why giving 65.00 instead of 64.35 in minus mode?

  8. #8
    Registered User
    Join Date
    10-22-2018
    Location
    Turkey
    MS-Off Ver
    Office2010
    Posts
    84

    Re: Possible Formula to calculate described probabilities

    My problem is definitely solved after davsth post thanks both one more again. Regards

  9. #9
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Possible Formula to calculate described probabilities

    can you clarify your logic for altering the calculation? see post 6

  10. #10
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Possible Formula to calculate described probabilities

    Your formula works fine for +10% of comission but why giving 65.00 instead of 64.35 in minus mode?
    The notion of a negative commission escapes me, but if there were such a thing, it would be calculated the same as a positive one.

  11. #11
    Registered User
    Join Date
    10-22-2018
    Location
    Turkey
    MS-Off Ver
    Office2010
    Posts
    84

    Re: Possible Formula to calculate described probabilities

    Dear shg our system here in tourism business must calculate commissions included or exluded while calculating the holiday packages. When I serve our packages to retailers they mostly want commissions included packages means I must reach my base rates (our netto that all discounts applied except for commission) after subtracting of commission. The second formula in post 6 just answering what I want to realize.

  12. #12
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Possible Formula to calculate described probabilities

    Gald you got what you needed.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. how to work out probabilities ?
    By jayb77 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-08-2018, 06:59 PM
  2. formula to determine probabilities
    By kjrmitch in forum Excel General
    Replies: 1
    Last Post: 10-20-2015, 09:54 PM
  3. [SOLVED] Probabilities
    By DaxtonAllen in forum Excel General
    Replies: 8
    Last Post: 03-30-2012, 03:10 PM
  4. How to calculate total probability of loss from combination of probabilities
    By prr in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-09-2009, 06:48 PM
  5. "Probabilities" formula
    By lu007 in forum Excel General
    Replies: 4
    Last Post: 09-08-2009, 12:24 PM
  6. Calculate Probabilities in percent
    By mremixer in forum Excel General
    Replies: 11
    Last Post: 12-22-2008, 12:50 AM
  7. Probabilities
    By phil2006 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-22-2006, 12:10 PM

Tags for this Thread

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