countif based on calculated field

[jafar]

I have a field that calculates a value from a range of fields:

[BA2]=(AX2/49)/49*2*10

where [AX2]=SUM(A2:AW2)

where A2:AW2 is a sorted list of numbers.

Now, I want to count how many numbers in the list are below m
calculated value BA2:

[BC2]=COUNTIF(A2:AW2,"<BA2")

The count however is 0, even though there are actually 4 numbers tha
are below BA2 (whose value is 25.3). If I replace BA2 in the conditio
with the value 25.3:

[BC2]=COUNTIF(A2:AW2,"<25.3")

it works. But it doesn't work if I leave it as BA2.

I need to use this in 50 rows, and then I have to repeat for differen
sets of data, so I can't keep typing in the value every time. Is ther
any way to do what I am trying to do?

(I'm using Excel 2002

 

[Trevor Shuttleworth]

Try:

[BC2]=COUNTIF(A2:AW2,"<"&BA2)

Regards

Trevor

 

[jafar]

Trevor, thanks, that worked perfectly