Lookup to determine if less than or greater than

[potta]

i've got myself confused...

i have two worksheets, simplified they are as follows:

sheet 1 contains the raw data:

service number(f) catergory(g) currency(h)
001 Housing £10.00
002 Support £20.00
003 Refuge £30.00

sheet 2 contains a formula in column k to give the 0.75 percentile value for each catergory from the currency value in sheet 1, entered as a CSE:

catergory(j) percentile(k)
row1 Housing ={percentile(if(sheet_1!$G$1:$G$500=sheet_2!J2,sheet1!$H$1:$H$500),0.75)}
row2 Support ={percentile(if(sheet_1!$G$1:$G$500=sheet_2!J3,sheet1!$H$1:$H$500),0.75)}
row3 Refuge ={percentile(if(sheet_1!$G$1:$G$500=sheet_2!J4,sheet1!$H$1:$H$500),0.75)}

all I want to do is add a colum i into sheet one which tells me if that service is above or below the sheet_2 percentile value for it's particular catergory.

CAn anyone help please??!

[NBVC]

How do you know if it is "above or below"? What are you comparing to?

[potta]

sheet 1 column i would note whether the currency value in sheet 1 column h is greater than or equal to than the appropriate percentile value for each catergory in sheet 2.


so column i needs to identify the catergory, correct percentile and compare to the appropriate currency.

[potta]

example file attached

thanks

[NBVC]

Try:

Please Login or Register  to view this content.

Note: I've also considered the possibility that the costs are equal.

[potta]

NBVC, thats great - thank you.