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))),""),"-")
HansVogelaar has given you a literal translation of the formula. I'm going to try to put it into English words, which is a way of thinking that I'd commend to you if you are going to be writing formulas yourself. Spell out in words what you want to do, and only after that convert it to cell references and the like.
The IF part of the formula is essentially checking to make sure
- Unit Count (B27) contains a number greater than 0 AND
- ACO Attachment (B20) is less than or equal to Attachment (W2) AND
- ACO Attachment (B20) plus ACCO Limit is greater than or equal to Upper Limit (W1)
Since all of those are true, all conditions are met, the computation can be executed. If any one of those conditions is not met, then the formula yields a blank. (That's what "" ends up meaning). This formula, the cell references adjusting as you move down, are, I'm sure, in the rest of columns C and D, but because the rest of column B is itself blank, that's why all the other cells in columns C and D are also blank. I'm pretty sure they have variations of this same formula in them, but the first condition there isn't met (column B, rows 28 through 34 are blank). If you entered a number in any of them, a number greater than zero, they too would become populated).
The computational part of the formula says
- Multiply the Unit Count (B27) [which in this case is the value of 10]
- By the value you find in the table (second image) where PPT and 1 x 1 intersect
- (HansVogelaar has explained how that intersection is found)
- [which in this case is 169]
- giving the result of 10 x 169 or 1690
==========================================
Now, very frankly, I don't know the full context here, but it really looks to me as if whoever created that formula and the rest of the spreadsheet was making it more complicated than it needs to be. Two of the conditions specified in the IF statement (those having to do with the ACO attachment and ACO limit seem entirely redundant, in that the values that have been entered into those cells seem designed to be right at the limit. If, in fact, those values on the main active part of the spreadsheet can and do vary in a meaningful way, then by all means leave those conditions there, but given that they seem like extreme outer limits which are being easily met, all they're doing is making the formula unnecessarily long and harder to read.