N() Function and Offset

[themev]

I received a spreadsheet and am trying to understand a block of formulas. Can someone explain to me what this formula is actually doing?

Cell F18 =SUMPRODUCT($C10:F10,N(OFFSET(F3,0,-COLUMN($C3:F3)+COLUMN($C3))))

How does this formula work? The second 'array' doesn't appear to be an array.

I wish I could figure out how to put a picture of my spreadsheet into this post, but I cannot seem to use the HTML sheets at work.

This is C3:F3:
2.0000 3.0000 4.0000 4.0000
This is C10:F10
- - 1.000 2.000

and the formula returns a value of 7.00. I cant figure out how though.

Any help would be appreciated.
Thanks

 

[Eric W]

	C	D	E	F
3	2	3	4	4
4
5
6
7
8
9
10			1	2
11
12
13
14
15
16
17
18				7

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet6​

Worksheet Formulas Cell Formula F18 =SUMPRODUCT($C10:F10,N(OFFSET(F3,0,-COLUMN($C3:F3)+COLUMN($C3)))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

It's calculating C10*F3+D10*E3+E10*D3+F10*C3, or 0*4+0*4+1*3+2*2 = 7.

The OFFSET is used to reverse C3:F3 by starting at F3 and using the -COLUMN($C3:F3)+COLUMN($C3) structure to generate {0,-1,-2,-3}, giving {4,4,3,2}. The N() function converts the spaces to zeros.

Hope this helps!

 

[themev]

Eric - wow. thank you, thank you, thank you! Now I see what they are doing.

I spent way too much time trying to figure it out (to no avail).

 

[Eric W]

Glad to help!