Forum Discussion
Confuse about formula
Hi I took the screenshot of the fields from my file, can anyone please how the value in "27C" been calculated.
Fomula:
=IFERROR(IF(AND($B27>0,$B$20<=W$2,$B$21+$B$20>=W$1),$B27*(INDEX($S$7:$V$15,MATCH($A27,$R$7:$R$15,0),MATCH(C$26,$S$5:$V$5,0))),""),"-")
In your screenshots:
B27=10, so B27>0.
B20=1,000,000 and W2=1,000,000 so B20<=W2.
B21=1,000,000, so B21+B20=2,000,000. and W1=2,000,000, so B21+B20>=W1.
Combining these, AND($B27>0,$B$20<=W$2,$B$21+$B$20>=W$1) evaluates to TRUE, so the formulas evaluates $B27*(INDEX($S$7:$V$15,MATCH($A27,$R$7:$R$15,0),MATCH(C$26,$S$5:$V$5,0))).
A27="PPT". This is found in R7, so MATCH($A27,$R$7:$R$15,0)=1.
C26="1 x 1". This is found in S5, so MATCH(C$26,$S$5:$V$5,0)=1.
INDEX($S$7:$V$15,MATCH($A27,$R$7:$R$15,0),MATCH(C$26,$S$5:$V$5,0)) returns the cell in the 1st row and 1st column of S7:V15, i.e. 169.
B27=10, as we already noted, so the product returned is 10*169 = 169.