Logical Meaning of Formula

**kiktuo** · 05-23-2011, 05:38 AM

Good Morning,

I got a formula from these forums that in essense calculates RANKIF.

=IF(B6<>"business services","",SUMPRODUCT(($B$6:$B$100="business services")*(AC6<$AC$6:$AC$100))+1)

The formula works great but could some tell me in english how this works? The bit im confused about is how (AC6<$AC$6:$AC$100))+1) is working to rank the values.

Thanks,

Kiktuo

**martindwilson** · 05-23-2011, 08:00 AM

=IF(B6<>"business services","",
SUMPRODUCT(($B$6:$B$100="business services")*(AC6<$AC$6:$AC$100))
+1)
the + 1 is just added to the result of

SUMPRODUCT(($B$6:$B$100="business services")*(AC6<$AC$6:$AC$100))
which in itself is the sum of rows in ac6:ac100 which are greater than ac6 and corresponding cell in row b6:b10 ="business services(since ac6 is in there it will always be = so thats probably why 1 is added on)
not knowing whats in cells ac6:ac100 i cant tell you any more.

**kiktuo** · 05-23-2011, 08:54 AM

Hi there and thanks for your reply.

In Column AC are a list of Salaries that are to be ranked.

Why does AC1(for example) <ac1:ac100 rank them?

it is the < that is confusing me ie why saying that ac1 is less than the totol of that column ranks them?