Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Long condition, using IF
hi guys, i need your help. im using excel for our payroll. we have different basic rate per outlet. is "if" condition limited as to the length of statement/condition? here's the formula that i cannot resolve since the excel cannot accept it. what should be the formula when making long condition? please revise my formula. many thanks! A outlet MLA binan iloilo davao cebu cdo pam baguio MLA_TL binan_TL iloilo_TL davao_TL cebu_TL cdo_TL pam_TL baguio_TL BINAN_MLA =IF(A12="MLA",357.5,(IF(A12="binan",304.7,(IF(A12= "iloilo",230,(IF(A12="davao",246.4,(IF(A12="cebu", 255.2,(IF(A12="cdo",239.8,(IF(A12="pam",300,(IF(A1 2="baguio",300,(IF(A12="MLA_TL"=401.50,(IF(A12="BI NAN_TL",401.50,(IF(A12="ILOILO_TL",308,(IF(A12="DA VAO_TL",308,(IF(A12="CEBU_TL",308,(IF(A12="CDO_TL" ,308,(IF(A12="PAM_TL",401.50,(IF(A12="BAGUIO_TL",4 01.50))))))))))))))) -- si_ako ------------------------------------------------------------------------ si_ako's Profile: http://www.excelforum.com/member.php...o&userid=35078 View this thread: http://www.excelforum.com/showthread...hreadid=558300 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Long condition, using IF
please make your formula by your own, I put an example =LOOKUP(A12,{"MLA","binan","iloilo"},{357.5,304.7, 230}) apply this short formula and see the result and increase values in accordingly. si_ako Wrote: hi guys, i need your help. im using excel for our payroll. we have different basic rate per outlet. is "if" condition limited as to the length of statement/condition? here's the formula that i cannot resolve since the excel cannot accept it. what should be the formula when making long condition? please revise my formula. many thanks! A outlet MLA binan iloilo davao cebu cdo pam baguio MLA_TL binan_TL iloilo_TL davao_TL cebu_TL cdo_TL pam_TL baguio_TL BINAN_MLA =IF(A12="MLA",357.5,(IF(A12="binan",304.7,(IF(A12= "iloilo",230,(IF(A12="davao",246.4,(IF(A12="cebu", 255.2,(IF(A12="cdo",239.8,(IF(A12="pam",300,(IF(A1 2="baguio",300,(IF(A12="MLA_TL"=401.50,(IF(A12="BI NAN_TL",401.50,(IF(A12="ILOILO_TL",308,(IF(A12="DA VAO_TL",308,(IF(A12="CEBU_TL",308,(IF(A12="CDO_TL" ,308,(IF(A12="PAM_TL",401.50,(IF(A12="BAGUIO_TL",4 01.50))))))))))))))) -- starguy ------------------------------------------------------------------------ starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434 View this thread: http://www.excelforum.com/showthread...hreadid=558300 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Long condition, using IF
Directly
=LOOKUP(A12,{"MLA","binan","iloilo","davao","cebu" ,"cdo","pam","baguio","MLA _TL","BINAN_TL","ILOILO_TL","DAVAO_TL","CEBU_TL"," CDO_TL","PAM_TL","BAGUIO_T L"}, {357.5,304.7,230,246.4,255.2,239.8,300,300,401.5,4 01.5,308,308,308,308,401.5 ,401.5}) but it would be better to put the two arrays, the text and the numbers, into separate ranges, say M1:M16 and N1:N16 and use these =LOOKUP(A12,M1:M16,N1:N16) it will be more maintainable -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "si_ako" wrote in message ... hi guys, i need your help. im using excel for our payroll. we have different basic rate per outlet. is "if" condition limited as to the length of statement/condition? here's the formula that i cannot resolve since the excel cannot accept it. what should be the formula when making long condition? please revise my formula. many thanks! A outlet MLA binan iloilo davao cebu cdo pam baguio MLA_TL binan_TL iloilo_TL davao_TL cebu_TL cdo_TL pam_TL baguio_TL BINAN_MLA =IF(A12="MLA",357.5,(IF(A12="binan",304.7,(IF(A12= "iloilo",230,(IF(A12="dava o",246.4,(IF(A12="cebu",255.2,(IF(A12="cdo",239.8, (IF(A12="pam",300,(IF(A12= "baguio",300,(IF(A12="MLA_TL"=401.50,(IF(A12="BINA N_TL",401.50,(IF(A12="ILOI LO_TL",308,(IF(A12="DAVAO_TL",308,(IF(A12="CEBU_TL ",308,(IF(A12="CDO_TL",308 ,(IF(A12="PAM_TL",401.50,(IF(A12="BAGUIO_TL",401.5 0))))))))))))))) -- si_ako ------------------------------------------------------------------------ si_ako's Profile: http://www.excelforum.com/member.php...o&userid=35078 View this thread: http://www.excelforum.com/showthread...hreadid=558300 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Long condition, using IF
thanks a lot guys. i copied the formula you have given, but some values are not displayed as what is stated in the formula. like for example, in MLA, the amount is not 357.50 and i still have some #N/A error. thanks! -- si_ako ------------------------------------------------------------------------ si_ako's Profile: http://www.excelforum.com/member.php...o&userid=35078 View this thread: http://www.excelforum.com/showthread...hreadid=558300 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Long condition, using IF
Sorry, should be sorted
=LOOKUP(A12,{"baguio","BAGUIO_TL","binan","BINAN_T L","cdo","CDO_TL","cebu"," CEBU_TL","davao","DAVAO_TL","iloilo","ILOILO_TL"," MLA","MLA_TL","pam","PAM_T L"}, {300,401.5,304.7,401.5,239.8,308,255.2,308,246.4,3 08,230,308,357.5,401.5,300 ,401.5}) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "si_ako" wrote in message ... thanks a lot guys. i copied the formula you have given, but some values are not displayed as what is stated in the formula. like for example, in MLA, the amount is not 357.50 and i still have some #N/A error. thanks! -- si_ako ------------------------------------------------------------------------ si_ako's Profile: http://www.excelforum.com/member.php...o&userid=35078 View this thread: http://www.excelforum.com/showthread...hreadid=558300 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Long condition, using IF
i got it. thanks a lot! -- si_ako ------------------------------------------------------------------------ si_ako's Profile: http://www.excelforum.com/member.php...o&userid=35078 View this thread: http://www.excelforum.com/showthread...hreadid=558300 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format as a MACRO | Excel Worksheet Functions | |||
More than 3 conditional formats? | Excel Discussion (Misc queries) | |||
Date & Time | New Users to Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions |