Confuse about formula

Arsalan975 

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

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):

1. save a copy of the original formula
2. 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)
3. and so forth.