Long condition, using IF

S

si_ako

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(A12="baguio",300,(IF(A12="MLA_TL"=401.50,(IF(A12="BINAN_TL",401.50,(IF(A12="ILOILO_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.50)))))))))))))))
 
S

starguy

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

Bob Phillips

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,401.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)
 
S

si_ako

thanks a lot guys.

i copied the formula you have given, but some values are not displaye
as what is stated in the formula. like for example, in MLA, the amoun
is not 357.50 and i still have some #N/A error. thanks
 
B

Bob Phillips

Sorry, should be sorted

=LOOKUP(A12,{"baguio","BAGUIO_TL","binan","BINAN_TL","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,308,230,308,357.5,401.5,300
,401.5})

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Top