Confused on a function

[daddioja]

Another forum user helped me out with this formula. It works great fo
my application, but I don't completely understand it. Particularly,
am unsure of the use of the brackets at the beginning and end of th
formating. Can someone clear this up for me? Thanks, Jason

{=SUM(IF(B2>B$2:B$21,1/COUNTIF(B$2:B$21,B$2:B$21)))+1+ROW()/10^10

 

[ghostwriter]

Another forum user helped me out with this formula. It works great for
my application, but I don't completely understand it. Particularly, I
am unsure of the use of the brackets at the beginning and end of the
formating. Can someone clear this up for me? Thanks, Jason

{=SUM(IF(B2>B$2:B$21,1/COUNTIF(B$2:B$21,B$2:B$21)))+1+ROW()/10^10}

It tells excel to consider the material inside the brackets as an array
and to perform all of the fuctions on the array not an individual
value. They are quite difficult to understand and not something I make
much use of. Essentially its a way to do several calculations on a
large amount of numbers without needing to copy equations or do
multiple steps. Anything an array can do can be done without them but
sometime it takes a lot more steps.

Ghostwriter

 

[Guest]

The brackets at the beginning and end of the function indicate this is an
array function. I could explain how array functions work, but microsoft does
a better job.

http://office.microsoft.com/en-us/assistance/HA010872901033.aspx

However I will tell you that if you are attempting to edit the array formula
you are working with and are having trouble getting the brackets to re-appear
and have the function work. You need to hit the keystroke combination
<Ctrl> + <Shift> + <Enter> when exiting the formula. This is how you tell
Excel to calculate the formula as an array formula.

 

[daddioja]

Thanks to both of you for your help. That was my problem. Now I get it
at least now I can work with it.: