Swanand

I think I understand what you're trying to do.

give this a shot:

=INDEX($A$100G$200,SMALL(IF($A$100A$200="Revenue",ROW($A$100A$200)),ROW(1:1)),7)

Use ctrl+shift+enter when you type it in to make it an array. Then you'll have to drag it down in order to get the revenue numbers in a row.

I would put this all in one separate column (for example column X).

Then you simply do a =median(X1:X100) or whatever it is and you should have the median number.

Edit: This basically looks at column A, finds the word revenue, and brinks back the corresponding number in column G. The array allows you to bring all of these revenue numbers into one column and right after one another which then makes calculations easy.

If this doesn't work, I'll try to play around with it, but I don't have access to private emails here at work so I can't check the file even if you do send it.



Edited 1 time(s). Last edit at Monday, August 15, 2011 at 02:03PM by SocratesCFA.