• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate reward on two conditions

YasserKhalil

YasserKhalil

Well-Known Member
Hello everyone
In my attachment I have start date and end date in cells C1 & C2 and in cells C4:C6 the difference between these two dates
I have two cases :
**************
(1) First case if years are less than 3
/////////////////////////////////////////
I need to calculate the reward in that way (cell C7 would contain the formula)
I have 1 Year / 6 Months / 13 Days
So the manual calculation in this case :
Salary * 1 * ((1) + (6/12) + (13/365))
-------------------------------------------------------------
1 After Salary * refers to one salary only in this case
Second one ((1) refers to the number of years in C4
The number 6 refers to the number of months in C5
12 (The number of the months of the year)
13 refers to the number of days in C6
365 refers to the days of the year


(1) Second case if years are greater than 3
///////////////////////////////////////////////
I need to calculate the reward in that way (cell C7 would contain the formula but I put some input in column E for just clarification)
I have in the example 5 Year / 8 Months / 18 Days
So the manual calculation in this case

(Salary * 1 * 3) >> This is the first part .. I mean to take the first three years and multiply them by 1 * 500
Take the rest after subtracting the three years (which will be 2 Years / 8 Months / 18 Days) and calculate them like that
(Salary * 2 * ((2) + (8/12) + (18/365))
2 >> refers to the second case .. here two salaries not just one as the years greater than 3
The second 2 >> refers to the number of years after subtracting 5 - 3
8 >> refers to the number of the months
18 >> refers to the number of days
12 (the number of the months of the year) & 365 (the number of days of the year)
so the final manual calculation for the second case like that
(Salary * 1 * 3) + (Salary * 2 * ((2) + (8/12) + (18/365))
----------------------------------------------------------------


** The first case using excel
Code: 
=ROUND(C3*1*((C4)+(C5/12)+(C6/365)),2)
The result would be : 767.81

** The second case using excel
Code: 
=ROUND((E3*1*3)+(E3*2*((E4-3)+(E5/12)+(E6/365))),2)
The result would be : 4215.98

I need one formula that achieve that task


Thanks advanced for help
 

Attachments

  • Rew.xlsm
    10.1 KB · Views: 8
Last edited:
W/o getting too fancy, could do:

=IF(C4>3,
ROUND(C3*1*((C4)+(C5/12)+(C6/365)),2),
ROUND((C3*1*3)+(C3*2*((C4-3)+(C5/12)+(C6/365))),2))

I don't really understand why you're including the *1 portion in an equation.
 
As far as i understood...

=INT((C2-C1)/365.25/3)*3*C3+(((C2-C1)/365.25)-3*INT(((C2-C1)/365.25)/3))*C3*INT(((C2-C1)/365.25)-3*INT(((C2-C1)/365.25)/3))
 
I wouldn't simplify to this if the bonus scheme equations can change, but here goes. the 2nd equation can be simplified algebraically to:
=(-3)S + 2S(Y + M/12 + D/365)
the first is:
=S(Y + M/12 + D/365)

To combine the two in smallest length, and round to 2 decimals:
=ROUND(-3*C3*(C4>3)+(1+(C4>3))*C3*(C4+C5/12+C6/365),2)
or in XL format
=ROUND(-3*C3*(C4>3)+(1+(C4>3))*C3*(C4+C5/12+C6/365),2)
 
Thank you very much for these great contributions
As for Luke's formula .. they gave me right results as expected for both cases
You asked me about * 1 (we multiply the first three years by one salary and the rest by two salaries)

As for Deepak's formulas they are nearby in results but not exactly as expected
I got 766.6 while it is expected to be 767.81 .. the same for the second case

Thank you very much for great help
 
Sorry for disturbing you again
I have the following inputs
C3 : 500
C4: 3
C5: 4
C6: 5

I tried the formulas provided here but didn't get exact result
The result would be
=(C3* 1 * C4)+(C3 * 2 * (C5/12) + (C6/365))
It would be 1833.35
 
In this example, C4 is not greater than 3, hence the formulas presented are not calculating the same...to start.

Bigger problem, you also have a parenthesis out of place. The way you have it written, only the C5/12 ratio is being multiplied against the C3 * 2. If you move the parenthesis to this:
=(C3* 1 * C4)+ C3*2*((C5/12) + (C6/365))

You'll get a correct result of 1847.03

To adjust my formula so that it is >= 3 years, new universal formula is:
=ROUND(-3*C3*(C4>=3)+(1+(C4>=3))*C3*(C4+C5/12+C6/365),2)
 
Thanks a lot for quick reply
Look in simple words ( 3 Years / 4 Months / 5 Days ) this is greater than three not less than three
So the number three years would be multiplied by one salary
what is over three years is (4 Months / 5 days)
These would be calculated in different method
(C5/12) + (C6/365)
and the result would be multiplied by two salaries as these exceed (over ) the three years
Hope it is clear now

Simply we don't deal only with C4 to determine if it is greater than three years or less than three years
 
Exactly. I was just saying that from the formula's math perspective, it was different. New formula should work for you.
 
I don't get it
I tried the formula
Code: 
=ROUND(-3*C3*(C4>=3)+(1+(C4>=3))*C3*(C4+C5/12+C6/365),2)
and I got 1847.03 while expected result is 1833.35
Have I missed anything?
I need to follow this logic
Code: 
=(C3* 1 * C4)+(C3 * 2 * (C5/12) + (C6/365))
 
I think you are right Mr. Luke
I am very sorry it was my fault
Your formula is working very well and as expected
Thanks a lot for this wonderful help
 
Sorry for disturbing you again Mr. Luke
This formula works well
Code: 
=ROUND(-3*C3*(C4>=3)+(1+(C4>=3))*C3*(C4+C5/12+C6/365),2)
It deals with one salary for the first three years and two salaries for the rest of the period

What If I need to deal with half salary for the first three years and one salary for the rest of the period
Thanks advanced for help
 
yes you are right
I didn't pay attention to that post .. I thought this formula is the only correct
Code: 
=ROUND(-3*C3*(C4>=3)+(1+(C4>=3))*C3*(C4+C5/12+C6/365),2)
Just for curiosity is it possible to edit this formula with the new argumenst
 
Certainly. If formula for a person under 3 years is still:
=S * (Y + M/12 + D/365)

and we want to combine that with a new formula for over 3 years, which starts as:
=0.5S + S * [(Y-3) + M/12 + D/365]

The second equation can be re-arranged to this:
=(-2.5)S + S * (Y + M/12 + D/365)

New combined equation then is:
=ROUND(-2.5S * (Y>=3) + S*(Y + M/12 + D/365)
whose XL formula is:
=ROUND(-2.5*C3*(C4>=3) + C3*(C4+C5/12+C6/365),2)

Hopefully that better shows how equation is formed, if you go this route. Again, if things can change, stick with the simpler IF formula, as it's easier to handle the 2 different equations.
 
You must log in or register to reply here.
Back
Top