Forum Discussion
Confuse about formula
Hi guys! can someone help please, I am trying to understand how the value is calculated in a particular cell. The formula is as below:
=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))),""),"-")
Can anyone elaborate to me please. Thanks
The formula checks whether B27 is positive AND B20 is less than or equal to W2 AND the sum of B21 and B20 is greater than or equal to W1.
If not, the formula returns the empty string "".
If yes, the formula tries to look up the value of A27 in R7:R15 and the value of C26 in S5:V5.
If both are found, the formula returns the value in S7:V15 in the corresponding row and column.
If at least one of those two is not found, IFERROR causes the formula to return "-".
I like your use of a visual for the INDEX/MATCH aspect of the mystery formula.
But, Hans!, you missed the part where you're to take whatever value that INDEX/MATCH formula retrieves and multiply it by $B27.
=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))),""),"-")
Between the two of us, though, I think we did alright.
I'd still like to know what's actually IN those columns; what kind of information is being manipulate here? Currency, Inventory, .... ?
Thanks, John. I concentrated so hard on trying to explain the INDEX/MATCH combination that I forgot the multiplication.
It sounds as if you're being asked to use (or maybe even improve) a spreadsheet that somebody else created. My sympathies!
It's hard to describe meaningfully what a formula like that is doing without knowing the column headings for the cells being referred to. So, for example, if certain conditions are met, the calculation per se is performed by this formula
$B27*(INDEX($S$7:$V$15,MATCH($A27,$R$7:$R$15,0),MATCH(C$26,$S$5:$V$5,0)))
It could be put in words as follows:
under certain conditions
- multiply the value in column B, row 27 with a specific cell in the range $S$7 to $V$15
- determine which cell in $S$7:$V$15 by finding the row and column determined by
- getting the row # from the range $R$7:$R$15k that matches whatever is in column A, row 27
- and the column # from the range $S$5:$V$5 that matches whatever is in column C, row 26
- determine which cell in $S$7:$V$15 by finding the row and column determined by
the conditions under which you do that calculation are....
determined by this
IF(AND($B27>0,$B$20<=W$2,$B$21+$B$20>=W$1)
which means, in effect, do that calculation above only IF
- B27 is greater than 0, AND
- B20 is less than or equal to W2, AND
- B21 plus B20 is greater than or equal to W1
The IFERROR part of it just gives you a "-" if the main formula itself results in an error.
And you need to realize that this formula can be copied to other rows and when that happens the row and column designations that aren't preceded by a $ sign will change.
IF that doesn't make sense, and IF you would not be violating confidentiality or privacy by doing so, I or somebody else could do a more meaningful translation if you could post a copy of the actual spreadsheet on OneDrive or GoogleDrive, with a link pasted here that grants access.
Here's a way to figure this kind of thing out for yourself (over and beyond using a resource like this to understand what each of the functions (INDEX, MATCH, etc) means):
- save a copy of the original formula
- Take each component of the full formula as a separate formula and see what result it produces
- e.g., =MATCH(C$26,$S$5:$V$5,0) yield? All by itself. Hint: it's going to be a number, #2
- do the same with =MATCH($A27,$R$7:$R$15,0) #1
- and then put the two numbers into this, replacing the MATCH sections with the plain numbers
- =INDEX($S$7:$V$15,#1,#2)
- and so forth.
- multiply the value in column B, row 27 with a specific cell in the range $S$7 to $V$15
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.
Arsalan975 I’m on my phone at the moment, with its limited keyboard, so not about to attempt a full explanation. But if you read through the responses you’ve already gotten, replacing the abstract cell references with the content from the various cells, you should be able to figure it out.
If you haven’t by the time I get to a desktop, I’ll chime in again.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.
Thanks averybody for your time and I really appriciate your efforts. By your help I was able to solve the formula.
Thanks and Regards
Arsalan
