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

Do you think its a good idea for Amazon to divide its new second headquarters between two locations?

Discuss in The Lounge

Poll History