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
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
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.44H4: =(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
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
Right. See the formula.
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.44H4: =(A4 * (1 - B4) * (1 - C4) - D4) / (1 - E4) 5 100.00 ₺ 15% 10% 5.00 ₺ -10% 64.35 65.00
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.
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?
My problem is definitely solved after davsth post thanks both one more again. Regards
can you clarify your logic for altering the calculation? see post 6
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.Your formula works fine for +10% of comission but why giving 65.00 instead of 64.35 in minus mode?
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.
Gald you got what you needed.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks