Ranking Data

mechpm · May 16th, 2002, 05:21 PM

I have a worksheet were I track the last ten bids for public projects. I use this to find the money left on the table( difference between my low bid and the second lowest bidder) I am able to tighten up my profits and still remain low Bidder on all of my projects.

In column F I have the % differences ( last ten bids ) I need to rank these from 1 thru 10 in Column G.

Then from there I need to add the 5th and 6th in the ranking and divide witch I think can handle. However, any help with a formula that will pick the 5th & 6th #s , / 2 will be helpful. I can’t find any useful info. on ranking #s in a list

xl · May 16th, 2002, 05:26 PM

Use rank.

Yogi Anand · May 16th, 2002, 05:52 PM

In addition to XL's suggestion on using the RANK function, you can also use the SMALL function. If you want to rank the 10 bids, in ascending order, with the lowest to the highest, use the following formula in cell G2

{=SMALL(F2:F11,ROW(A1:A10))}

Highlite cells G2:G11 and array enter ... CTRL+SHIFT+ENTER

To find the 5th smallest number from F2:F11
use the following formula:

=SMALL(F2:F11,5)

Regards!

May 16th, 2002, 05:52 PM	#3
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	In addition to XL's suggestion on using the RANK function, you can also use the SMALL function. If you want to rank the 10 bids, in ascending order, with the lowest to the highest, use the following formula in cell G2 {=SMALL(F2:F11,ROW(A1:A10))} Highlite cells G2:G11 and array enter ... CTRL+SHIFT+ENTER To find the 5th smallest number from F2:F11 use the following formula: =SMALL(F2:F11,5) Regards! __________________ Regards! Yogi Anand, D.Eng, P.E. Energy Efficient Building Network LLC www.energyefficientbuild.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 16th, 2002, 05:21 PM	#1
mechpm New Member Join Date: May 2002 Posts: 30	I have a worksheet were I track the last ten bids for public projects. I use this to find the money left on the table( difference between my low bid and the second lowest bidder) I am able to tighten up my profits and still remain low Bidder on all of my projects. In column F I have the % differences ( last ten bids ) I need to rank these from 1 thru 10 in Column G. Then from there I need to add the 5th and 6th in the ranking and divide witch I think can handle. However, any help with a formula that will pick the 5th & 6th #s , / 2 will be helpful. I can’t find any useful info. on ranking #s in a list

May 16th, 2002, 05:26 PM	#2
xl New Member Join Date: May 2002 Posts: 10	Use rank.