Terimakasih Banyak atas penjabarannya Mr.Kid....
Njenengan Terbaiek
Pada Selasa, 28 Januari 2020 17.05.34 WIB, 'Mr. Kid' [email protected]
[XL-mania] <[email protected]> menulis:
larik (array) hasil sum yang bergerak seperti SUM($D2:D2) , SUM($D2:E2) , dst
bisa diperoleh dengan cara mengalikan array data (matriks data 1 x N) dengan
array segitiga atas bernilai 1 (N x N)array data (matriks 1 x N) : d2:j2array
segitiga atas bernilai 1 (matriks N x N) :
(column($d$1:$j$1)>=Transpose(column($d$1:$j$1)))*1perkalian matriksnya :
=mmult( d2:j2 , (column($d$1:$j$1)>=Transpose(column($d$1:$j$1)))*1 )
dari hasil larik SUM($D2:D2) , SUM($D2:E2) , dst yang disusun dengan perkalian
matriks, akan dicari yang memenuhi nilai P persen dari totaltotal :
=sum(d2:j2)P persen total (misal P=95%): =0.95*sum(d2:j2)
dikatakan memenuhi jika setiap nilai (item) dalam larik hasil sum [hasil
perkalian matriks] adalah >= nilai P persen total =mmult( d2:j2 ,
(column($d$1:$j$1)>=Transpose(column($d$1:$j$1)))*1 ) >= 0.95*sum(d2:j2)
hasilnya larik true atau falsesupaya mudah dikalkulasi, true diubah jadi 1, dan
false diubah jadi 0, menjadi : =1* ( mmult( d2:j2 ,
(column($d$1:$j$1)>=Transpose(column($d$1:$j$1)))*1 ) >= 0.95*sum(d2:j2) )
didapat larik berisi 1 atau 0, mulai dari 0 untuk yang tidak memenuhi dan 1
untuk yang memenuhi secara berurutan dari hasil larik sum terendah ke tertinggi
yang dicari adalah yang memenuhi pertama, berarti yang bernilai 1 yang pertama
kali ditemukan.=Match( 1 , 1* ( mmult( d2:j2 ,
(column($d$1:$j$1)>=Transpose(column($d$1:$j$1)))*1 ) >= 0.95*sum(d2:j2) )
, 0 )hasilnya adalah lokasi item pertama yang memenuhi
hasil lokasi ini digunakan untuk mengambil nilai yang dibutuhkan dari
d1:j1=Index( $d$1:$j$1 , Match( 1 , 1* ( mmult( d2:j2 ,
(column($d$1:$j$1)>=Transpose(column($d$1:$j$1)))*1 ) >= 0.95*sum(d2:j2) )
, 0 ) )
karena prosesnya melibatkan komputasi array, maka formula di-entry sebagai
array formula (tekan CTRL SHIFT ENTER) menggantikan penekanan ENTER umumnya
On Tue, Jan 28, 2020 at 3:26 PM [email protected] [XL-mania]
<[email protected]> wrote:
Terimakasih Kakak.... iya selama ini saya mengunkana manual dg IF sum, akan
tetapai IF sum hanya berlaku hingga header ke -11 saja. nah bagaimana jika
Headernya 30 an.
Pada Selasa, 28 Januari 2020 14.44.08 WIB, Raden Saputra
[email protected] [XL-mania] <[email protected]> menulis:
Dear [email protected],
coba formula :
"=IF(SUM($D2:D2)>=95;"0,01";IF(SUM($D2:E2)>=95;"0,03";IF(SUM($D2:F2)>=95;"0,05";IF(SUM($D2:G2)>=95;"0,07";IF(SUM($D2:H2)>=95;"0,85";IF(SUM($D2:I2)>=95;"0,87";IF(SUM($D2:J2)>=95;"0,89";"")))))))"
Regards
Raden
On Tue, Jan 28, 2020 at 10:36 AM [email protected] [XL-mania]
<[email protected]> wrote:
Mohon maaf sebelumnya, karena infonya tidak detail, pada intinya saya hendak
mencari jumlah dari Header D1 Ke Header J1 yang jumlahnya minimal >=95..
Misalkan :
ADI => sum (D2: J2) yang >= 95 , ternyata ada di Header 0.03 (E1)
Pada Intinya mencari jumlah maximum >=95 ada di header mana.
Pada Jumat, 24 Januari 2020 15.05.51 WIB, Idrus, Adi
<[email protected]> menulis:
Ini maksudnya gimana ya bu? bisa diperjelas kah
From: [email protected] [mailto:[email protected]]
Sent: Friday, January 24, 2020 10:35 AM
To: [email protected]
Subject: [SPAM] ]] XL-mania [[ Menentukan Nilai Maximum [1 Attachment]
Sahabat XL Mania,
saya ada kendala mengenai penggunaan nilai maximum di XL, mohon di bantu
bagamana cara menetukan nilai Maximum, jika menggunakan IF(sum) akan ada
limited di kolom 11
terimakasih atas bantuannya
--
Raden Saputra
#yiv0329651225 #yiv0329651225 -- #yiv0329651225ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv0329651225
#yiv0329651225ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv0329651225
#yiv0329651225ygrp-mkp #yiv0329651225hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv0329651225 #yiv0329651225ygrp-mkp #yiv0329651225ads
{margin-bottom:10px;}#yiv0329651225 #yiv0329651225ygrp-mkp .yiv0329651225ad
{padding:0 0;}#yiv0329651225 #yiv0329651225ygrp-mkp .yiv0329651225ad p
{margin:0;}#yiv0329651225 #yiv0329651225ygrp-mkp .yiv0329651225ad a
{color:#0000ff;text-decoration:none;}#yiv0329651225 #yiv0329651225ygrp-sponsor
#yiv0329651225ygrp-lc {font-family:Arial;}#yiv0329651225
#yiv0329651225ygrp-sponsor #yiv0329651225ygrp-lc #yiv0329651225hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv0329651225
#yiv0329651225ygrp-sponsor #yiv0329651225ygrp-lc .yiv0329651225ad
{margin-bottom:10px;padding:0 0;}#yiv0329651225 #yiv0329651225actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv0329651225
#yiv0329651225activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv0329651225
#yiv0329651225activity span {font-weight:700;}#yiv0329651225
#yiv0329651225activity span:first-child
{text-transform:uppercase;}#yiv0329651225 #yiv0329651225activity span a
{color:#5085b6;text-decoration:none;}#yiv0329651225 #yiv0329651225activity span
span {color:#ff7900;}#yiv0329651225 #yiv0329651225activity span
.yiv0329651225underline {text-decoration:underline;}#yiv0329651225
.yiv0329651225attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv0329651225 .yiv0329651225attach div a
{text-decoration:none;}#yiv0329651225 .yiv0329651225attach img
{border:none;padding-right:5px;}#yiv0329651225 .yiv0329651225attach label
{display:block;margin-bottom:5px;}#yiv0329651225 .yiv0329651225attach label a
{text-decoration:none;}#yiv0329651225 blockquote {margin:0 0 0
4px;}#yiv0329651225 .yiv0329651225bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv0329651225
.yiv0329651225bold a {text-decoration:none;}#yiv0329651225 dd.yiv0329651225last
p a {font-family:Verdana;font-weight:700;}#yiv0329651225 dd.yiv0329651225last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv0329651225
dd.yiv0329651225last p span.yiv0329651225yshortcuts
{margin-right:0;}#yiv0329651225 div.yiv0329651225attach-table div div a
{text-decoration:none;}#yiv0329651225 div.yiv0329651225attach-table
{width:400px;}#yiv0329651225 div.yiv0329651225file-title a, #yiv0329651225
div.yiv0329651225file-title a:active, #yiv0329651225
div.yiv0329651225file-title a:hover, #yiv0329651225 div.yiv0329651225file-title
a:visited {text-decoration:none;}#yiv0329651225 div.yiv0329651225photo-title a,
#yiv0329651225 div.yiv0329651225photo-title a:active, #yiv0329651225
div.yiv0329651225photo-title a:hover, #yiv0329651225
div.yiv0329651225photo-title a:visited {text-decoration:none;}#yiv0329651225
div#yiv0329651225ygrp-mlmsg #yiv0329651225ygrp-msg p a
span.yiv0329651225yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv0329651225
.yiv0329651225green {color:#628c2a;}#yiv0329651225 .yiv0329651225MsoNormal
{margin:0 0 0 0;}#yiv0329651225 o {font-size:0;}#yiv0329651225
#yiv0329651225photos div {float:left;width:72px;}#yiv0329651225
#yiv0329651225photos div div {border:1px solid
#666666;min-height:62px;overflow:hidden;width:62px;}#yiv0329651225
#yiv0329651225photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv0329651225
#yiv0329651225reco-category {font-size:77%;}#yiv0329651225
#yiv0329651225reco-desc {font-size:77%;}#yiv0329651225 .yiv0329651225replbq
{margin:4px;}#yiv0329651225 #yiv0329651225ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv0329651225 #yiv0329651225ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv0329651225
#yiv0329651225ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv0329651225
#yiv0329651225ygrp-mlmsg select, #yiv0329651225 input, #yiv0329651225 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv0329651225
#yiv0329651225ygrp-mlmsg pre, #yiv0329651225 code {font:115%
monospace;}#yiv0329651225 #yiv0329651225ygrp-mlmsg *
{line-height:1.22em;}#yiv0329651225 #yiv0329651225ygrp-mlmsg #yiv0329651225logo
{padding-bottom:10px;}#yiv0329651225 #yiv0329651225ygrp-msg p a
{font-family:Verdana;}#yiv0329651225 #yiv0329651225ygrp-msg
p#yiv0329651225attach-count span {color:#1E66AE;font-weight:700;}#yiv0329651225
#yiv0329651225ygrp-reco #yiv0329651225reco-head
{color:#ff7900;font-weight:700;}#yiv0329651225 #yiv0329651225ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv0329651225 #yiv0329651225ygrp-sponsor
#yiv0329651225ov li a {font-size:130%;text-decoration:none;}#yiv0329651225
#yiv0329651225ygrp-sponsor #yiv0329651225ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv0329651225
#yiv0329651225ygrp-sponsor #yiv0329651225ov ul {margin:0;padding:0 0 0
8px;}#yiv0329651225 #yiv0329651225ygrp-text
{font-family:Georgia;}#yiv0329651225 #yiv0329651225ygrp-text p {margin:0 0 1em
0;}#yiv0329651225 #yiv0329651225ygrp-text tt {font-size:120%;}#yiv0329651225
#yiv0329651225ygrp-vital ul li:last-child {border-right:none
!important;}#yiv0329651225
