Hi There, I'm trying to work out an excel formula that will show me a number based upon information from another worksheet in the same book.

Basically, I have 14 people who through other formulas each have an average rating, but for my front cover of the work book, I want to show the following information;

Percentage of TL's ranked 2 or less

Percentage of TL's ranked 3

Percentage of TL's ranked 4 or moreI can't seem to figure out what formula I need though to show me this information.

I've tried;

=SUM(Sheet18!E5:E18,"<2")

=SUM(Sheet18!E5:E18)<2=IF(Sheet18!E5:E18,"<2")

=IF(Sheet18!E5:E18)<2=SUM(Sheet18!E5:E18/14)

however once all my source information is generated, it won't show me a number that gives me an indication of how many out of 14 are ranked <2, 3, 4> as a percentage.

Any help would be greatly appreciated.

Many thanks

Have you tried the COUNTIF function? https://support.office.com/en-us/ar...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

There is also the =RANK() function. With your data like:

A B 1) Values Rank High to Low 2) 7 4 3) 4 5 4) 25 1 5) 8 3 6) 16 2In cell B2 enter the formula: =RANK(A2,$A$2:$A$6)

then drag down and you get the above.

You can then simply sort on the rank column.MIKE

message edited by mmcconaghy

Ask Your Question

Weekly Poll

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History