Dear excelers, Saat kita mempergunakan formula yg banyak apakah ada caranya supaya ringkas sama seperti halnya kita meringkas base data utk vlookup pada name manager formula detil seperti begini :
=IF(OR(LEFT(E9,6)="Acting",LEFT(E9,7)="Asisten"),0,IF(ISERROR(IF(AN9>0,VLOOKUP($E9,gradeA,2,FALSE)*AN9,0)+IF(AO9>0, VLOOKUP($E9,gradeA,4,FALSE)*AO9,0)+IF(AP9>0,VLOOKUP($E9,gradeA,5,FALSE)*AP9,0)+IF(AQ9>0,VLOOKUP($E9,gradeA,6,FALSE)*AQ9,0)+ IF(AR9>0,VLOOKUP($E9,gradeA,7,FALSE)*AR9,0)+IF(AS9>0,VLOOKUP($E9,gradeA,8,FALSE)*AS9,0)+IF(AT9>0,VLOOKUP($E9,gradeA,9,FALSE)*AT9,0)),0,((IF(AN9>0,VLOOKUP($E9,gradeA,2,FALSE)*AN9,0)+IF(AO9>0,VLOOKUP($E9,gradeA,4,FALSE)*AO9,0)+IF(AP9>0,VLOOKUP($E9,gradeA,5,FALSE)*AP9,0)+ IF(AQ9>0,VLOOKUP($E9,gradeA,6,FALSE)*AQ9,0)+IF(AR9>0,VLOOKUP($E9,gradeA,7,FALSE)*AR9,0)+IF(AS9>0,VLOOKUP($E9,gradeA,8,FALSE)*AS9,0)+IF(AT9>0, VLOOKUP($E9,gradeA,9,FALSE)*AT9,0))))) contoh formula dibawah ini : bagaimana caranya sama seperti di name manager utk contih formula (IF(AN9>0,VLOOKUP($E9,gradeA,2,FALSE)*AN9,0)+IF(AO9>0, VLOOKUP($E9,gradeA,4,FALSE)*AO9,0)+IF(AP9>0,VLOOKUP($E9,gradeA,5,FALSE)*AP9,0)+IF(AQ9>0,VLOOKUP($E9,gradeA,6,FALSE)*AQ9,0)+ IF(AR9>0,VLOOKUP($E9,gradeA,7,FALSE)*AR9,0)+IF(AS9>0,VLOOKUP($E9,gradeA,8,FALSE)*AS9,0)+IF(AT9>0,VLOOKUP($E9,gradeA,9,FALSE)*AT9,0))menjadi 1 nama di name manager misal a yg di inginkan kita tinggal panggil "a": =IF(OR(LEFT(E9,6)="Acting",LEFT(E9,7)="Asisten"),0,IF(ISERROR(a),0,(a) Demikian disampaikan, mohon solusi dan pencerahannya kepada rekan2 excelers mania. terimakasih Salam Hangat, Ries_Afir@h
