• 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.

re allocation

pallu

Member
Hi Team

I need formula of reallocation the based on the change month and percentage detailed attached in the mail Pls help fourmula calculating automatically

Regards
pallu
 

Attachments

  • Re alloacation .xlsx
    9.8 KB · Views: 0
Hi Pallu,
See if this works:

=IF(OR(E14={"chennai","Mumbai","Delhi"}),LOOKUP(G14,{1,37,49},{35,23.1,11.6}),LOOKUP(G14,{1,37,49},{30,19.8,9.9}))/100

Regards,
 
I used a 2 way lookup table to simplify the calculations
upload_2015-5-26_21-19-43.png
See attached file:
 

Attachments

  • Re alloacation.xlsx
    10.7 KB · Views: 0
hi
super thanks it works fine, need some fine tune i will try my self if not i will ask you help
thanks for your support

Regards
Pallu
 
PFA,

=IF(AND(G14<=36,OR(E14="chennai",E14="Mumbai",E14="Delhi")),35%,IF(AND(G14<=36,E14<>"chennai",E14<>"Mumbai",E14<>"delhi"),30%,IF(AND(G14>=37,G14<=48,OR(E14="chennai",E14="Mumbai",E14="Delhi")),23.1%,IF(AND(G14>=37,G14<=48,E14<>"chennai",E14<>"Mumbai",E14<>"delhi"),19.8%,IF(AND(G14>=49,G14<=60,OR(E14="chennai",E14="Mumbai",E14="Delhi")),11.6%,IF(AND(G14>=49,G14<=60,E14<>"chennai",E14<>"Mumbai",E14<>"delhi"),9.9%))))))
 

Attachments

  • Re alloacation .xlsx
    9.5 KB · Views: 0
Pallu

Just a comment
Both these solutions will give you the right answer
Khallid's is simple for the specific task given
My solution is much more scaleable if you need to add other cities or rates in the future
 
Hi

attached file i need formula for which date and month completed as per the data Pls help i have colored in yellow for formula required column
Regards
Pallu
 

Attachments

  • Re alloacation 2.xlsx
    12.1 KB · Views: 0
On the Reallocation sheet E14:
=INDEX(workings!$E$6:$E$100,MATCH(A14,Relocation!$A$14:$A$100,0))

Copy E14 down
Copy H14 down
 
Change the Number format of E14 to dd/mm/yyyy

Select E14
Ctrl+1
Number
Custom
dd/mm/yyyy
Ok
 
Hi
I need a formula from starting date 01/01/2014 to End date is will based on the months and date will change automatically
Up to 36 months
37 months to 48 months
49 months to 60 months
 
It is very unclear what your requirements are
Do you mean like this ?
 

Attachments

  • Copy of Re alloacation 2.xlsx
    13.2 KB · Views: 1
Back
Top