Comparison Problem for tender bids

**bartman** · 11-12-2006, 09:42 AM

Hi there and thank goodness I found this

forum, I am trying to do the following comparison of bids in a to make it fair. hopefully this can be done easily

Recived 7 bids and have to assigned a "rating value" (RV) to each bid.
Each bid is to receive a value of 0 to 20 based upon the value of the bid with the lowest receiving "20" and highest "0".
Everyone else receives part of of the RV based upon the difference from the lowest bid until the it's value is exhausted. an example would be , lowest = 20, highest = 0, if the spread was 20 then the mid bid would have a RV of 10.
Easy to say but how can I put this into a formula

Hopefully I explained my problem and any help would is greatly appreciated

**VBA Noob** · 11-12-2006, 09:57 AM

Hi,

If your bids in A1 to A7 then enter this in B1 and drag down

=CHOOSE(RANK(A1,$A$1:$A$7),0,INT(20/COUNTA(A:A)*2),INT(20/COUNTA(A:A)*3),INT(20/COUNTA(A:A)*4),INT(20/COUNTA(A:A)*5),INT(20/COUNTA(A:A)*6),INT(20/COUNTA(A:A)*7))

it returns

0 Lowest Bid
5
8
11
14
17
20 Highest Bid

VBA Noob

**bartman** · 11-12-2006, 01:15 PM

Thank you for this formula and approach
It does what I want it to do in that it assigns full value to the lowest bid and zero to highest.

1, If I require to exclude the bids greater than say 20% from the point awarding would the addition of an IF statement be called for and what would it look like

2, my other question would the awarding of points based upon how far a bid is from the lowest bid . the model shown is linear and how would the formula appear if the differences of each bid is taken in to account. so the higher the bid the less points received, but if 2 bid are close then they are awarded more points for being closer to the low value. Hope i have not

any one

I am new at this and have been asked to help create fair method of evaluating bids so I an looking at several methods

Thank you for your help so far ...

**VBA Noob** · 11-12-2006, 08:05 PM

Hi,

Not sure how you want it weighted so you need to come up with that criteria.

So without the weighting you can yuse these. Once you workout the weighting you could add a lookup ??

This formula will rank them for you

=RANK(A1,$A$1:$A$7,1)
e.g Lowest bid will return 1

and this will will show "<< Void over 20% of lowest bid !!" if > 20%

=IF(A1/SMALL($A$1:$A$7,(ROW($A$1)))-100%>20%,"<< Void over 20% of lowest bid !! ",A1)

VBA Noob