Calculate % with help of nested if function

[Gaurang Mhatre]

Dear Friends,

I am facing problem in nested if function to calculate % as per the bucket.

It would be great if you could solve my query.

Attached is excel for your reference.

Thanks in advance for your help and time.


Regards,

Gaurang

 

[Nebu]

Hi:

Try this formula in H2

=IF(AND(F2>=LEFT($F$8,1)+0,F2<=RIGHT($F$8,2)+0),$G$8,IF(AND(F2>=LEFT($F$9,2)+0,F2<=RIGHT($F$9,2)
+0),$G$9,IF(AND(F2>=LEFT($F$10,2)+0,F2<=RIGHT($F$10,2)+0),$G$10,IF(AND(F2>=LEFT($F$11,2)+0,F2<=RIGHT($F$11,2)+0),$G$11))))*G2

Thanks

 

[Syedali]

My try
=IF(AND(F2>=1,F2<=12),G2*10%,IF(AND(F2>=13,F2<=24),G2*20%,IF(AND(F2>=25,F2<=36),G2*30%,IF(AND(F2>=37,F2<=48),G2*40%))))

 

[Khalid NGO]

Hi Gaurang,
My attempt:

=G2*LOOKUP(F2,{1,13,25,37},G8:G11)

Regards,

 

[Faseeh]

This one also..

=LOOKUP(F2,{0,13,25,37,100},{0.1,0.2,0.3,0.4,0.4})

 

[Khalid NGO]

This one also..

=LOOKUP(F2,{0,13,25,37,100},{0.1,0.2,0.3,0.4,0.4})

Hi Faseeh bhai,
I think you forgot to Multiply G2 with LOOKUP

 

[Faseeh]

Yes, Khalid

 

[Syedali]

Hi Gaurang,
My attempt:

=G2*LOOKUP(F2,{1,13,25,37},G8:G11)

Regards,

Great Khalid, How you people are thinking in awesome way.

Great Great Great.

 

[Gaurang Mhatre]

Thanks alot Nebu, Syedali, Khalid & Faseeh for taking out your time and helping me out. Its working.

Appreciate your help!
God bless!
Regards,
Gaurang Mhatre

 

[Khalid NGO]

Great Khalid, How you people are thinking in awesome way.

Great Great Great.

Thanks Ali,
Credit goes to Chandoo.org, their Ninjas, and some awesome members.

 

[akinkaraman]

Great Khalid, How you people are thinking in awesome way.

Great Great Great.

They are genius :O

 

[Gaurang Mhatre]

Hi Khalid,

How will u give bucket to the attached timings. i have used above if and formula, but not working.

Please find attached excel.
Thanks alot in advance for taking out time.

Regards,
Gaurang

 

[Khalid NGO]

Hi Khalid,

How will u give bucket to the attached timings. i have used above if and formula, but not working.

Please find attached excel.
Thanks alot in advance for taking out time.

Regards,
Gaurang

Hi Gaurang,
Can you check this:

=LOOKUP(LEFT(C7,9),{"17:00:01","23:00:01","12:00:01"},$D$7:$D$9)
Format to time

Still working on it.

Regards,

 

[Faseeh]

Lets try this: if A5 contains time then it should be

=LOOKUP(A5,{0,0.50001157,0.708344907407407,0.95833333333,0.99999999999999},{"2:00 PM","6:30 PM","9:00 AM","2:00 PM"})