find the previous number divisble by 5 before a provided number...

Try this formula in C2 copied down

=LOOKUP(C2,INT(COUNT(C:C)/5)*{0,1,2,3,4}+1,{"A","B","C","D","E"})

This should give the distribution you want, e.g. for 248 records as per your example this will give 49 of each of A, B, C and D and 52 E grades.

Note that this distribution might vary if you have ties in column B because then you will have duplicate ranks but I would suggest that that would be better than giving different grades to 2 records with identical number of households....

Originally Posted by daddylonglegs

Try this formula in C2 copied down

=LOOKUP(C2,INT(COUNT(C:C)/5)*{0,1,2,3,4}+1,{"A","B","C","D","E"})

This should give the distribution you want, e.g. for 248 records as per your example this will give 49 of each of A, B, C and D and 52 E grades.

Note that this distribution might vary if you have ties in column B because then you will have duplicate ranks but I would suggest that that would be better than giving different grades to 2 records with identical number of households....

You were great Daddy..

Daddy Daddy Daddy cool--Boney M

I acknowledge the fact that u also considered about duplicate ranks as I was wondering that...

Now that I have this portion solved by you...

I would like to get one more thing I want to get the last values for each grade from the column B before the grade change takes place..

Like the last value in column B adjacent before the grade changes from "A" to "B".

LOOKUP works a little like VLOOKUP with a final argument of TRUE. Lookup range has to be sorted ascending [which is OK here because you have all "A"s followed by "B"s etc. so data is sorted]

Two big advantages over a normal VLOOKUP. This is the "vector" form of LOOKUP (see Excel help) which allows you to specify a return range that's to the left of the lookup range...and rather than matching with the first instance of "A" this will match with the last instance, which is what you want.

Another way (which doesn't depend on the order of any of the data) would be to use this array formula

=MIN(IF(D2:D1000="A",B2:B1000))

confirmed with CTRL+SHIFT+ENTER

For your second question....

If you have a zero in Y1 then in Y2:Y5

=LOOKUP("D",D2:D1000,B2:B1000)
=LOOKUP("C",D2:D1000,B2:B1000)
=LOOKUP("B",D2:D1000,B2:B1000)
=LOOKUP("A",D2:D1000,B2:B1000)

this will give you an ascending range in Y1:Y5

In Z1:Z5 put the 5 grades in reverse order so the range Y1:Z5 looks like

0 E
26387 D
49019 C
90235 B
151855 A

Then, if you have a figure to grade in Y7 use this formula

=LOOKUP(Y7,Y1:Z5)

Can u please help me with your way in the file that I am attaching again...

I accidentaly attached to the earlier reply...

Please offer help and also the explanation as always...

I will now start using Lookup more often after this---after your doctrine..

Just one more thing daddy..

Based on your formula [ LOOKUP("A",D2:D1000,B2:B1000) ], I wud be getting 5 ranges...

I want to use these ranges in a formula in such a way that these ranges change columnwise and anything fallin in these ranges gets an "A","B","C" etc. for the final rating..

Now what we graded was the historic data, based on the ranges acheived for the historic data we need to us e these ranges for new data.


Thanks